PostgreSQL

PostgreSQL で LIKE を使わずに複数の値の部分一致検索を行う

PostgreSQL で複数の LIKE 条件を使う

SQL で部分一致検索を行う場合には通常 LIKE を使いますが、１つの列に対して複数の値を LIKE で検索しようとすると OR や AND を使って次のように書きます。 以下は name 列に対して '%re%' という部分一致検索と '%w%' という部分一致検索を実行する例です。

=> SELECT * FROM colors WHERE name LIKE '%re%' OR name LIKE '%w%';

しかし何度も同じ列に対して条件を書いていくのは非常に退屈で骨の折れる作業です。そこで今回は１つの条件で複数の LIKE 検索を行う方法を記載します。

テスト用のテーブルとデータを用意

ではテストするためにテスト用のテーブルとデータを登録します。

=> CREATE TABLE colors (id serial, name text NOT NULL);
CREATE TABLE

=> 
INSERT INTO colors (name) VALUES
  ('red'),
  ('blue'),
  ('yellow'),
  ('orange'),
  ('white'),
  ('black'),
  ('pink'),
  ('gray');

INSERT 0 8

=> SELECT * FROM colors;
+----+--------+
| id |  name  |
+----+--------+
|  1 | red    |
|  2 | blue   |
|  3 | yellow |
|  4 | orange |
|  5 | white  |
|  6 | black  |
|  7 | pink   |
|  8 | gray   |
+----+--------+
(8 rows)

colors というテーブルに８レコードを登録しました。

複数の値の部分一致検索を行う

ここでは LIKE を使わずに、１つの列に対して複数の部分一致検索を行ってみましょう。

これを実現するためには、PostgreSQL の正規表現と配列機能を利用します。

 結論からすると以下のように記述すると name 列に対して '%re%' という部分一致検索と '%w%' という部分一致検索を１つの条件で記載しています。条件はいくらでも追加できます。

=> SELECT * FROM colors WHERE name ~~* ANY(ARRAY['%re%', '%w%']);
+----+--------+
| id |  name  |
+----+--------+
|  1 | red    |
|  3 | yellow |
|  5 | white  |
+----+--------+
(3 rows)

この機能と同等のことを他のデータベース(Oracle や MySQL、MS SQL Server 等)で可能なのかどうか検証していませんが、PostgreSQL データベースではこのような部分一致検索が 可能です。