PostgreSQLでのあいまい検索

曖昧検索について

SQLで曖昧検索をするには、LIKE句を使用するのが一般的であるが、Postgresqlでは別の書き方もできるので、メモ程度に残していこうと思う。

演算子を使った曖昧検索

• 以下のレコードが投入されているテーブルがあるとする。

id	language
1	Java
2	JavaScript
3	Ruby
4	PHP

-- LIKE句を用いたSQL
SELECT * FROM hoge WHERE name LIKE 'Java%'

-- Postgresqlの演算子を用いたSQL
SELECT * FROM hoge WHERE name ~~ 'Java%'

上記のSQLを実行した場合両方同一の結果が得られる

+------------------+
| id | language    |   
+------------------+
| 1   | Java       |
| 2   | JavaScript |
+------------------+

大文字小文字を区別せずに曖昧検索をする

少し話がそれてしまうが、Postgresqlでは,曖昧検索で、大文字と小文字を区別せずに検索を行うためのILIKEが標準で実装されている。LIKE句に比べたら、そこまで使用頻度は高くないかもしれないが、知っておくことに越したことはない。

ユーザーが自由入力でき、大文字小文字の入力チェックを行わない項目には有効かもしれない。

先程のテーブルのレコードを少しばかり変更する。
Java → java
JavaScript → javascript

id	language
1	java
2	javascript
3	Ruby
4	PHP

-- ILIKE句を用いたSQL
SELECT * FROM hoge WHERE name ILIKE 'Java%'

-- Postgresqlの演算子を用いたSQL
SELECT * FROM hoge WHERE name ~~* 'Java%'

先ほどと同様に上記のSQLを実行した場合両方同一の結果が得られる

+------------------+
| id | language    |   
+------------------+
| 1   | Java       |
| 2   | javascript |
+------------------+

~~、~~*はいずれもPostgresql固有の演算子です。
もちろんNOT LIKE,NOT ILIKEと対になる!~~、!~~* 演算子もあります。

参考文献

PostgreSQL 12.3文書 第9章 関数と演算子 9.7. パターンマッチ1