かきスタンプ

福岡でフリーランスの物流系のエンジニアやってます。

Oracle:大文字/小文字 半角/全角 ひらがな/カタカナ を区別しないで検索

以下の関数を組み合わせて、大文字/小文字 半角/全角 ひらがな/カタカナ を区別せずに検索できます。

  • UPPER(小文字→大文字)
  • TO_MULTI_BYTE(半角→全角)
  • UTL_I18N.TRANSLITERATE(詳細は後述)

(例)
以下で、「大文字・全角・カタカナ」に変換しています。

--('target_character' が、検索対象文字です。)
UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE('target_character')),'kana_fwkatakana')

SQL出力例

CHAR

select
    UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE('a')) ,'kana_fwkatakana') as S1 -- 半角:a
   ,UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE('A')) ,'kana_fwkatakana') as S2 -- 半角:A
   ,UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE('')),'kana_fwkatakana') as S3 -- 全角:a
   ,UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE('')),'kana_fwkatakana') as S4 -- 全角:A
   ,UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE('')),'kana_fwkatakana') as S5 -- 全角:あ
   ,UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE('')),'kana_fwkatakana') as S6 -- 全角:ア
   ,UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE('')) ,'kana_fwkatakana') as S7 -- 半角:ア
from
    dual

<出力結果>

S1 S2 S3 S4 S5 S6 S7

NVARCHAR2
NVARCHAR2の場合、「半角→全角」への変換が上手く行かないので、to_charをかましてます。

select
    UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE(to_char(substr(COL1,1,1)))),'kana_fwkatakana') as V1 -- 半角:b
   ,UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE(to_char(substr(COL2,1,1)))),'kana_fwkatakana') as V2 -- 半角:B
   ,UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE(to_char(substr(COL3,1,1)))),'kana_fwkatakana') as V3 -- 全角:b
   ,UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE(to_char(substr(COL4,1,1)))),'kana_fwkatakana') as V4 -- 全角:B
   ,UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE(to_char(substr(COL5,1,1)))),'kana_fwkatakana') as V5 -- 全角:い
   ,UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE(to_char(substr(COL6,1,1)))),'kana_fwkatakana') as V6 -- 全角:イ
   ,UTL_I18N.TRANSLITERATE(UPPER(TO_MULTI_BYTE(to_char(substr(COL7,1,1)))),'kana_fwkatakana') as V7 -- 半角:イ
from
    (
       select
            CAST('b: 半角'  as NVARCHAR2(100)) as COL1
           ,CAST('B: 半角'  as NVARCHAR2(100)) as COL2
           ,CAST('b: 全角' as NVARCHAR2(100)) as COL3
           ,CAST('B: 全角' as NVARCHAR2(100)) as COL4
           ,CAST('い: 全角' as NVARCHAR2(100)) as COL5
           ,CAST('イ: 全角' as NVARCHAR2(100)) as COL6
           ,CAST('イ: 全角'  as NVARCHAR2(100)) as COL7
       from
           dual
    ) TMP_VIEW01

<出力結果>

V1 V2 V3 V4 V5 V6 V7

UTL_I18N.TRANSLITERATE にて使用できる変換文字列

変換名文字列 説明
'kana_fwkatakana' 全角カタカナに変換
'kana_hwkatakana' 半角カタカナに変換
'kana_hiragana' 全角ひらがなに変換
'fwkatakana_hwkatakana' 全角カタカナのみを半角カタカナに変換
'fwkatakana_hiragana' 全角カタカナのみを全角ひらがなに変換
'hwkatakana_fwkatakana' 半角カタカナのみを全角カタカナに変換
'hwkatakana_hiragana' 半角カタカナのみを全角ひらがなに変換
'hiragana_fwkatakana' 全角ひらがなのみを全角カタカナに変換
'hiragana_hwkatakana' 全角ひらがなのみを半角カタカナに変換