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以外では、正規表現の対象は数字、アルファベット、記号にとどめておいたほうが無難。
