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


SQLの正規表現は、各DBMSが拡張で対応している
SQLでの正規表現は、ISO規格には定められていません。各DBMSが拡張機能として正規表現をサポートしています。
MySQLは、regexp演算子で正規表現対応。マルチバイト文字には未対応。
MySQLでは、regexp演算子で正規表現のマッチングが可能です。
- mysql> select * from employees where first_name regexp '^G.*' limit 10;
- +--------+------------+------------+-----------+--------+------------+
- | emp_no | birth_date | first_name | last_name | gender | hire_date |
- +--------+------------+------------+-----------+--------+------------+
- | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 |
- | 10015 | 1959-08-19 | Guoxiang | Nooteboom | M | 1987-07-02 |
- | 10055 | 1956-06-06 | Georgy | Dredge | M | 1992-04-27 |
- | 10063 | 1952-08-06 | Gino | Leonhardt | F | 1989-04-08 |
- | 10075 | 1960-03-09 | Gao | Dolinsky | F | 1987-03-19 |
- | 10121 | 1962-07-14 | Guoxiang | Ramsay | M | 1989-05-03 |
- | 10124 | 1962-05-23 | Geraldo | Marwedel | M | 1991-09-05 |
- | 10133 | 1963-12-12 | Giri | Isaak | M | 1985-12-15 |
- | 10202 | 1956-01-05 | Greger | Lichtner | M | 1991-10-06 |
- | 10207 | 1955-05-28 | Girolamo | Anandan | F | 1992-10-11 |
- +--------+------------+------------+-----------+--------+------------+
上記は、first_nameの1文字目がGで始まるレコードを抽出する例です。
【関連記事】
▶sql likeを使ったサンプル集 likeは本当に遅いのか?
MySQLでは、regexpのシノニム(名前は違うが機能は同じ)としてrlikeが用意されています。regexpと同様、正規表現文字列を指定してマッチングが可能です。
- mysql> select * from employees where first_name rlike '^[^G]y.*' limit 10;
- +--------+------------+------------+---------------+--------+------------+
- | emp_no | birth_date | first_name | last_name | gender | hire_date |
- +--------+------------+------------+---------------+--------+------------+
- | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 |
- | 10125 | 1957-09-13 | Syozo | Hiltgen | F | 1990-10-26 |
- | 10132 | 1956-12-15 | Ayakannu | Skrikant | M | 1994-10-30 |
- | 10367 | 1955-12-24 | Hyuckchul | Gini | M | 1991-06-24 |
- | 10673 | 1959-12-26 | Hyuckchul | Casperson | M | 1990-03-01 |
- | 10701 | 1961-09-30 | Hyuncheol | Zolotykh | M | 1986-10-27 |
- | 10752 | 1964-07-18 | Byong | Conde | M | 1993-05-28 |
- | 10863 | 1962-08-05 | Byong | Bhattacharjee | M | 1985-12-25 |
- | 10922 | 1953-10-27 | Kyoichi | Wossner | M | 1992-08-09 |
- | 11005 | 1958-03-12 | Byong | Douceur | F | 1986-07-27 |
- +--------+------------+------------+---------------+--------+------------+
- 10 rows in set (0.00 sec)
しかし、MySQLではマルチバイト文字列には対応しておらず、想定通りの動きになりません。
参考)日本語文字列にREGEXPは使えないMySQL – dondari
あくまで、アルファベットや数字、記号のマッチングのみにとどめておいた方が良さそうです。
Oracleの正規表現は、REGEXP_LIKE
Oracleには、regexp_likeやregexp_replaceなど正規表現を組み込んだ機能拡張がなされています。
- SELECT names "names",
- REGEXP_REPLACE(names, '^(\S+)\s(\S+)\s(\S+)$', '\3, \1 \2')
- AS "names after regexp"
- FROM famous_people
- ORDER BY "names";
- 結果:
- names names after regexp
- -------------------- --------------------
- John Quincy Adams John Quincy Adams
- Harry S. Truman Truman, Harry S.
- John Adams John Adams
- John Quincy Adams Adams, John Quincy
- John_Quincy_Adams John_Quincy_Adams
- 5 rows selected.
ただし、やはり日本語文字列に対しては想定外のマッチングをするケースがあるようです。
SQL Serverは正規表現をサポートしていないが、likeで簡易的な正規表現が使用可能
SQL Serverでは、正規表現をサポートしていません。しかし、likeのマッチング文字列に、ワイルドカード以外に[…]を使って、文字の範囲指定が可能です。
- SELECT name FROM sys.databases
- WHERE name LIKE 'm[n-z]%';
関連)[ ] 文字列と一致するワイルドカード – SQL Server (Transact-SQL) | Microsoft Docs
PostgreSQLは、SIMILAR TO演算子等で正規表現に対応
PostgreSQLは、SIMILAR TO演算子や、~(チルダ)演算子で正規表現に対応しています。
- select 'abcde' ~ 'a.*e';
- ?column?
- --------
- true
- select 'キャノン' ~ 'キ[ヤャ]ノン';
- ?column?
- --------
- true
- select '山田 太郎 23' ~ '田.*2';
- ?column?
- --------
- true
- select '山田 太郎 23' ~ '^田.*';
- ?column?
- --------
- false
PostgreSQLでは、日本語での正規表現マッチングも問題なくおこなえるようです。(PostgreSQL 9.6で確認)
まとめ

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