Webサイト制作コースのお申し込みはこちら Webサイト制作コースのお申し込みはこちら

SQLの正規表現についてまとめています。

SQLの正規表現は、各DBMSが拡張で対応している

SQLでの正規表現は、ISO規格には定められていません。各DBMSが拡張機能として正規表現をサポートしています。

MySQLは、regexp演算子で正規表現対応。マルチバイト文字には未対応。

MySQLでは、regexp演算子で正規表現のマッチングが可能です。

  1. mysql> select * from employees where first_name regexp '^G.*' limit 10;
  2. +--------+------------+------------+-----------+--------+------------+
  3. | emp_no | birth_date | first_name | last_name | gender | hire_date  |
  4. +--------+------------+------------+-----------+--------+------------+
  5. |  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
  6. |  10015 | 1959-08-19 | Guoxiang   | Nooteboom | M      | 1987-07-02 |
  7. |  10055 | 1956-06-06 | Georgy     | Dredge    | M      | 1992-04-27 |
  8. |  10063 | 1952-08-06 | Gino       | Leonhardt | F      | 1989-04-08 |
  9. |  10075 | 1960-03-09 | Gao        | Dolinsky  | F      | 1987-03-19 |
  10. |  10121 | 1962-07-14 | Guoxiang   | Ramsay    | M      | 1989-05-03 |
  11. |  10124 | 1962-05-23 | Geraldo    | Marwedel  | M      | 1991-09-05 |
  12. |  10133 | 1963-12-12 | Giri       | Isaak     | M      | 1985-12-15 |
  13. |  10202 | 1956-01-05 | Greger     | Lichtner  | M      | 1991-10-06 |
  14. |  10207 | 1955-05-28 | Girolamo   | Anandan   | F      | 1992-10-11 |
  15. +--------+------------+------------+-----------+--------+------------+

上記は、first_nameの1文字目がGで始まるレコードを抽出する例です。

【関連記事】
▶sql likeを使ったサンプル集　likeは本当に遅いのか？ 

MySQLでは、regexpのシノニム（名前は違うが機能は同じ）としてrlikeが用意されています。regexpと同様、正規表現文字列を指定してマッチングが可能です。

  1. mysql> select * from employees where first_name rlike '^[^G]y.*' limit 10;
  2. +--------+------------+------------+---------------+--------+------------+
  3. | emp_no | birth_date | first_name | last_name     | gender | hire_date  |
  4. +--------+------------+------------+---------------+--------+------------+
  5. |  10005 | 1955-01-21 | Kyoichi    | Maliniak      | M      | 1989-09-12 |
  6. |  10125 | 1957-09-13 | Syozo      | Hiltgen       | F      | 1990-10-26 |
  7. |  10132 | 1956-12-15 | Ayakannu   | Skrikant      | M      | 1994-10-30 |
  8. |  10367 | 1955-12-24 | Hyuckchul  | Gini          | M      | 1991-06-24 |
  9. |  10673 | 1959-12-26 | Hyuckchul  | Casperson     | M      | 1990-03-01 |
  10. |  10701 | 1961-09-30 | Hyuncheol  | Zolotykh      | M      | 1986-10-27 |
  11. |  10752 | 1964-07-18 | Byong      | Conde         | M      | 1993-05-28 |
  12. |  10863 | 1962-08-05 | Byong      | Bhattacharjee | M      | 1985-12-25 |
  13. |  10922 | 1953-10-27 | Kyoichi    | Wossner       | M      | 1992-08-09 |
  14. |  11005 | 1958-03-12 | Byong      | Douceur       | F      | 1986-07-27 |
  15. +--------+------------+------------+---------------+--------+------------+
  16. 10 rows in set (0.00 sec)

しかし、MySQLではマルチバイト文字列には対応しておらず、想定通りの動きになりません。

参考）日本語文字列にREGEXPは使えないMySQL – dondari

あくまで、アルファベットや数字、記号のマッチングのみにとどめておいた方が良さそうです。

Oracleの正規表現は、REGEXP_LIKE

Oracleには、regexp_likeやregexp_replaceなど正規表現を組み込んだ機能拡張がなされています。

  1. SELECT names "names",
  2.   REGEXP_REPLACE(names, '^(\S+)\s(\S+)\s(\S+)$', '\3, \1 \2')
  3.     AS "names after regexp"
  4. FROM famous_people
  5. ORDER BY "names";
  6.  
  7. 結果:
  8.  
  9. names                names after regexp
  10. -------------------- --------------------
  11.  John Quincy Adams    John Quincy Adams
  12. Harry S. Truman      Truman, Harry S.
  13. John Adams           John Adams
  14. John Quincy Adams    Adams, John Quincy
  15. John_Quincy_Adams    John_Quincy_Adams
  16.  
  17. 5 rows selected.

関連）データベース・アプリケーションでの正規表現の使用

ただし、やはり日本語文字列に対しては想定外のマッチングをするケースがあるようです。

SQL Serverは正規表現をサポートしていないが、likeで簡易的な正規表現が使用可能

SQL Serverでは、正規表現をサポートしていません。しかし、likeのマッチング文字列に、ワイルドカード以外に[…]を使って、文字の範囲指定が可能です。

  1. SELECT name FROM sys.databases
  2. WHERE name LIKE 'm[n-z]%';

関連）[ ] 文字列と一致するワイルドカード – SQL Server (Transact-SQL) | Microsoft Docs

PostgreSQLは、SIMILAR TO演算子等で正規表現に対応

PostgreSQLは、SIMILAR TO演算子や、~（チルダ）演算子で正規表現に対応しています。

関連）PostgreSQL 公式：パターンマッチ

  1. select 'abcde' ~ 'a.*e';
  2.  
  3. ?column?
  4. --------
  5. true
  6.  
  7. select 'キャノン' ~ 'キ[ヤャ]ノン';
  8.  
  9. ?column?
  10. --------
  11. true
  12.  
  13. select '山田 太郎 23' ~ '田.*2';
  14.  
  15. ?column?
  16. --------
  17. true
  18.  
  19. select '山田 太郎 23' ~ '^田.*';
  20.  
  21. ?column?
  22. --------
  23. false

PostgreSQLでは、日本語での正規表現マッチングも問題なくおこなえるようです。（PostgreSQL 9.6で確認）

まとめ

ポテパンダの一言メモ
  • SQLの正規表現は、各DBMSが拡張機能として対応している
  • postgreSQLを除いて、日本語文字列への正規表現は未対応の模様。
  • postgreSQL以外では、正規表現の対象は数字、アルファベット、記号にとどめておいたほうが無難。

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか？

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。