Oracle でひらがな・全角カタカナ・半角カタカナ変換

英数字の半角・全角変換は TO_SINGLE_BYTE (半角化) や TO_MULTI_BYTE() (全角化) で可能。これに UPPER() (大文字化) や LOWER() (小文字化) を組み合わせれば、LIKE 演算子であいまい検索が可能になる。

しかし、これらはひらがなやカタカナの変換は行ってくれない。

そこで、UTL_I18N パッケージの TRANSLITERATE ファンクションを併用する。

SELECT
    UTL_I18N.TRANSLITERATE('ｱｲウエおか', 'kana_fwkatakana')
FROM
    DUAL;

こんな感じで、変換する文字列と変換モードを引数で指定する。引数の種類は以下などを参照。FW は「Full Width」の略で全角、HW は「Half Width」の略で半角、ってことが分かれば覚えやすいかな。

前述の英数字の変換と合わせると、こんな感じであいまい検索に使えそう。

SELECT
    NAME
FROM
    USER_MASTER
WHERE
    UTL_I18N.TRANSLITERATE( UPPER( TO_SINGLE_BYTE( NAME ) ), 'kana_fwkatakana') LIKE '%' || UTL_I18N.TRANSLITERATE( UPPER( TO_SINGLE_BYTE( /* 検索文字列 */ ) ), 'kana_fwkatakana') || '%'
;

検索対象のカラムと、検索文字列の両方を同じ形式に統一する、というワケだ。

スペースはまだ平気かと思うが、波ダッシュ (全角チルダ) とかハイフン・マイナス・ダッシュとかの解釈が組み合わせるプログラム側と合わないかもしれないので、よく見ながら使うことにする。

また、多少は性能劣化も起こるかもしれない。ご利用は計画的に。

その他参考