SQLで文字列の一部が一致するデータを抽出するなら、whereに続けてlikeで検索条件を使うのが一般的です。しかし、多くのデータベース管理ソフトでは、whereに続けて正規表現で検索できる関数があるのをご存じでしょうか。よく使われるデータベース管理ソフトの抽出条件の書き方について解説します。
SQLにおけるパターンマッチング
SQLのselect文は、データベースに格納された膨大なデータから、ある条件に一致したデータを抽出する処理です。そして、その条件とは、where句に続く完全一致をチェックする「=」を使うか、また文字の一部が一致するかをチェックするパターンマッチングの演算子の「like」を使うのが一般的です。
しかし、SQLに記述できるパターンマッチングの演算子は、likeだけではありません。データベース管理ソフトにもよりますが、similar to演算子やPOSIX様式の正規表現も利用できます。
伝統的なSQLのパターンマッチング演算子like
like演算子は、SQLが制定された当初からあるパターンマッチング演算子であり、SQLが使えるデータベース管理ソフトの全てで利用できます。そのため、whereに続く条件としてlike句を使っていれば、どのデータベース管理ソフトでも使えると考えてください。
なお、likeの機能は単純で、likeに続く文字列が含まれていれば真を返し、その逆のnot likeも利用できます。
【likeの使い方】
where 要素名 like パターン
where 要素名 not like パターン
なお、likeの使い方については、より詳しい記事がたくさんあるので、そちらも参照してください。
【SQL】LIKE句の基本的な使い方～複数検索する場合の方法まで解説
正規表現が使えるSQLのパターンマッチング演算子similar to
1999年に国際標準として規定されたSQL:1999は、当時主流だったプログラミング言語の考え方が幾つも採用されており、そういった追加された機能の1つが正規表現による値参照機能でした。そして、SQLで正規表現によるパターンマッチングするための演算子が、similar toです。
ただし、similar toは、like句によるパターンマッチングの機能に加えて、POSIX正規表現も扱えるように機能拡張した仕様になっており、厳密に言えば正しい正規表現を扱う関数とは言えません。もし、使うことになった場合は、注意してください。
similar to以外の正規表現を扱うパターンマッチング演算子
今よく利用されているデータベース管理ソフトには、多くの独自の拡張機能が組み込まれています。そして、その中には、正規表現を扱うパターンマッチング演算子もあり、いろいろなケースで利用されています。
例えば、正規表現を扱うパターンマッチング演算子のsimilar toが使えるデータベース管理ソフトは多くありません。ほどんど使えない、と言えるでしょう。しかし、MySQLやOracleデータベースでは、全く違う演算子が組み込まれています。さらに、マイクロソフト社のSQL Serverでは、like句で正規表現が使えたりします。このため、違うデータベース管理ソフト用のSQLで正規表現が使われている場合は、注意が必要です。
SQLによる正規表現によるパターンマッチングの違い
先ほど、SQLにはwhereに続くlike句の他に、プログラミング言語で使われる正規表現が扱えるパターンマッチング関数があると紹介しましたが、この関数を紹介しているWebページはlikeの紹介記事ほど多くありません。これは、正規表現によるパターンマッチング関数が、データベース管理ソフトによって違うのが原因です。
次に、よく使われているデータベース管理ソフトにおける正規表現の扱いの違いを解説します。
Oracleデータベースにおける正規表現の扱い
データベース管理ソフトの標準と言えるのが、エンタープライズ分野で導入実績の多いOracleデータベースです。しかし、このOracleデータベースでは、SQLの国際標準で定義されているsimilar toが使えません。Oracleデータベースでは、正規表現が扱える独自の関数を使います。
Oracleデータベースで、whereに続くlikeの代わりに正規表現によるパターンマッチングをするには、REGEXP_LIKEを使います。
【REGEXP_LIKEの使い方】
REGEXP_LIKE ( 対象の文字列式 , 正規表現パターン )
REGEXP_LIKEの例 select str from sample where REGEXP_LIKE(sample.str, '(\.jp$)' );
similar toが使えるのはPostgreSQL
SQL国際標準で規定されているsimilar toですが、利用者の多いデータベース管理ソフトの中で、これが使えるのはPostgreSQLだけです。なお、先ほども触れたようにsimilar toは、likeのパターンマッチングと正規表現の両方を記述できることから、正しい正規表現とは言えません。
さらにPostgreSQLでは、~演算子で正規表現の検索パターンを指定できます。そのため、similar toを利用する機会は無いかもしれません。
「~」を使って正規表現の例 select * from table where title ~ '[a-zA-Z]';
MySQLの正規表現
MySQLと互換性があるmariadbでは、正規表現によるパターンマッチングをする場合、regexp演算子またはrlike演算子を使います。
【regexp演算子とrlink演算子の使い方】
対象の文字列式 regexp 正規表現パターン
対象の文字列式 rlike 正規表現パターン
regexp演算子とrlink演算子を使った例 select * from table where title regexp '[a-z]'; select * from table where title rlike '[a-z]';
ただし、regexp演算子とrlike演算子は、MySQL、mariadbともバージョンによっては使える正規表現が制限されています。それぞれの最新版を利用してください。
なお、MySQLのバージョン8.0以降は、Oracleデータベースのregexp_likeが使えるようになりました。これは、Oracleデータベースと同じライブラリが使われているので、同じ結果が得られます。SQLを移植する際などに活用してください。
マイクロソフト社のSQL Serverはlikeのみ
マイクロソフト社のSQL Serverは、Windows Serverとセットで使われることが多く、エンタープライズ分野に強いOracleデータベースやオープンソースのMySQLとも違った進化を遂げたデータベース管理ソフトです。基本的には、SQLが使えるデータベース管理ソフトですが、多くの独自の機能が組み込まれいるのが特徴と言えます。
今回取り上げている抽出条件の書き方も他も違いがあり、like演算子でSQL国際標準のlikeの機能と、それに加えて正規表現の検索パターンも指定できます。厳密には、SQL Serverのlikeは、Transact-SQLと呼ばれるSQLを独自に拡張した機能の一つであり、「％」や「_」といった検索文字の他、[]や[^]などの正規表現のような書き方も可能です。
SQL Serverはlikeの使用例 select * from table where title like '[a-z]';
whereで指定する条件の難しさ
Webシステムでデータベースを活用する際、全てのデータをプログラム側に取り込み、そこから欲しいデータだけ抜き出して使うことは、それほど難しくはありません。しかし、この方法では、大きなデータを扱うことができず、さらに処理に時間がかかります。
しかし、SQLのwhereで条件を指定して、必要なデータのみ抽出するには、プログラムを書くようにはいきません。likeで対象を絞り、さらにandやorを組み合わせて条件に合った集合を作ることになりますが、SQL特有の難しかがあります。
その点、正規表現は、その書き方を覚えるのが大変ですが、書き方を習得してしまえば、かなり複雑な抽出条件でもシンプルに書くことが可能です。さらに、正規表現の書き方を覚えれば、プログラム言語でも応用できます。
SQLのlikeは、使い方が単純で覚えやすいのですが、likeだけで条件に合ったSQLを作るには経験が必要です。それなら、よく使われるデータベース管理ソフトで正規表現が使えるので、ぜひ、正規表現をマスターしましょう。
まとめ
データベース管理ソフトを利用するメリットは、大量のデータから効率良く必要な情報を抽出できる点です。そのため、where に続く抽出条件をいかに効率よく書けるかが、SQLを作るポイントと言えるでしょう。しかし、SQLの伝統的なlike演算子だけでは、抽出条件に合わせて書くのは大変なことです。
そのため、抽出条件をより効率的に書ける正規表現をマスターしましょう。もちろん、正規表現はプログラミング言語でも使えます。そして、SQLで大量のデータベースから必要なデータをうまく抽出し、プログラムでうまく活用できるようになりましょう。
