PostgreSQLで全角半角を区別しない問い合わせ

 

PostgreSQLは全角半角を区別します。つまり、"AA"で検索しても"ＡＡ"はヒットしません。このような検索を行う場合は、データベースに格納するデータそのものを自分の基準に従って投入しておく必要があります。

しかし、全角半角混在の元の状態も維持する必要があり、問い合わせ時のみこれらを区別したくない場合には次の方法が使えます。
※クエリ例で使用しているテーブルは、リンクテーブルではダメなのかで使用したものです。
データは文字の区別が検証できるように半角全角を混在させます。

商品cd	商品名
0	ダミー商品
1	糊
2	はさみ
3	定規
5	A4
6	B4
102	a4
103	Ａ４

テスト問い合わせ1

SELECT 商品cd, 商品名 FROM t_商品台帳 WHERE 商品名 LIKE 'A4%';

テスト問い合わせ1の結果

商品cd	商品名
5	A4

　

STEP.1 文字変換する関数を使う

ここで、商品名の英数字を全角変換してからLIKE評価するようにSQL文を書き換えると

テスト問い合わせ2

SELECT 商品cd, 商品名 FROM t_商品台帳
 WHERE translate(upper(商品名), '-0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ', '－０１２３４５６７８９ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ　') LIKE 'Ａ４%';

テスト問い合わせ2の結果

商品cd	商品名
5	A4
102	a4
103	Ａ４

　

STEP.2 関数を作成してSQL文を簡略にする

このままではSQL文を書くのが面倒なので、関数を作成してSQL文を簡略にします。

CREATE OR REPLACE FUNCTION get_nocase_text (text) RETURNS text AS $$ 
 SELECT translate(upper($1) 
 ,'-0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ' 
 ,'－０１２３４５６７８９ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ　'); 
$$ LANGUAGE SQL IMMUTABLE; 

と関数を作成しておくと、SQL文は次のようになります。

SELECT 商品cd, 商品名 FROM t_商品台帳 WHERE get_nocase_text(商品名) LIKE 'Ａ４%';

　

STEP.3 式インデックスで高速にする

特定の列について文字変換を伴う問い合わせを『頻繁に実行する場合』は、式に対するインデックスを作成すると、SQL文実行時に毎回文字変換する代わりに変換結果のインデックスが検索に使用されます。

CREATE INDEX t_商品台帳_商品名 ON t_商品台帳 (get_nocase_text(商品名));

　

カタカナとひらがなを区別せずに検索するための「カナかな変換」も関数の中身を書き換えれば可能ですが、半角カタカナは濁点・半濁点つきを全角一文字に変換する処理も行わないと期待どおりに検索できません。が、私には簡潔な変換処理関数は思いつきません。