ハヤシもはやしもしっかり抽出！PostgreSQLで賢く大文字小文字を無視した文字列検索

2024-06-24

PostgreSQLにおける大文字小文字を区別しない文字列比較

lower関数とILIKE句の使用

最も一般的な方法は、lower() 関数を使用して両方の文字列を小文字に変換し、その後 ILIKE 句で比較する方法です。

SELECT * FROM users WHERE name ILIKE '%ハヤシ%'

この例では、users テーブル内の name 列において、'ハヤシ'を含むすべてのレコードが抽出されます。大文字小文字が区別されないため、'ハヤシ'、'ハヤシ'、'HAYASHI' など、あらゆる表記のレコードが抽出されます。

citext型を使用

citext 型は、大文字小文字を区別しない専用の文字列型です。citext 型の列同士を比較する場合は、特別な句は必要ありません。

CREATE TABLE users (
  name citext NOT NULL
);

SELECT * FROM users WHERE name = 'ハヤシ';

この例では、users テーブル内の name 列において、'ハヤシ' と一致するレコードが抽出されます。citext 型を使用しているため、大文字小文字は区別されません。

POSIX正規表現を使用する方法もあります。この方法は、より複雑なパターンマッチングに適していますが、ここでは基本的な大文字小文字を区別しない比較のみを説明します。

SELECT * FROM users WHERE name REGEXP '[A-Za-z]*ハヤシ[A-Za-z]*';

この例では、users テーブル内の name 列において、'ハヤシ'を含むすべてのレコードが抽出されます。正規表現 [A-Za-z]* は、英字ゼロ個以上を含む任意の文字列にマッチします。

各方法の比較

lower() 関数と ILIKE 句: 最もシンプルで分かりやすい方法です。
citext 型: 大文字小文字を区別しない比較を頻繁に行う場合に適しています。
POSIX正規表現: より複雑なパターンマッチングが必要な場合に適しています。

PostgreSQLには、大文字小文字を区別しない文字列比較を行うための様々な方法があります。それぞれの方法には長所と短所があるため、状況に応じて適切な方法を選択する必要があります。

PostgreSQLにおける大文字小文字を区別しない文字列比較：サンプルコード

lower関数とILIKE句の使用

-- usersテーブルにnameカラムがある場合
-- nameカラムの値が'ハヤシ'を含むすべてのレコードを抽出する
SELECT * FROM users WHERE name ILIKE '%ハヤシ%';

citext型を使用

-- usersテーブルにnameカラムをcitext型で作成
CREATE TABLE users (
  name citext NOT NULL
);

-- nameカラムの値が'ハヤシ'と一致するレコードを抽出
SELECT * FROM users WHERE name = 'ハヤシ';

POSIX正規表現を使用

-- usersテーブルにnameカラムがある場合
-- nameカラムの値が'ハヤシ'を含むすべてのレコードを抽出
SELECT * FROM users WHERE name REGEXP '[A-Za-z]*ハヤシ[A-Za-z]*';

説明

上記のコードは、users という名前のテーブルに name という名前の列があることを前提としています。
name 列は、文字列データの格納に使用されます。
サンプルコードでは、3つの方法それぞれで name 列の値が 'ハヤシ' を含むレコードを抽出しています。
各方法の詳細については、上記の解説を参照してください。

補足

実際のコードでは、テーブル名や列名、抽出条件などを状況に合わせて変更する必要があります。
サンプルコードは、PostgreSQL 10.0以降で使用できます。
より複雑なパターンマッチングが必要な場合は、POSIX正規表現の機能をさらに活用することができます。

非決定論的照合順序を使用

PostgreSQL 12.0以降では、非決定論的照合順序を使用して、大文字小文字を区別しない比較を含む、より複雑な照合規則を定義できます。これは、citext 型よりも柔軟性が高く、より多くの Unicode の特殊文字を正しく処理することができます。

例:

CREATE COLLATION my_collation (
  locale = 'ja-JP',
  sort = pg_catalog.default_sort,
  character_set = pg_catalog.utf8,
  comparison = pg_catalog.case_insensitive_comparison
);

CREATE TABLE users (
  name varchar(255) COLLATE my_collation NOT NULL
);

SELECT * FROM users WHERE name = 'ハヤシ';