10-31 あいまい検索

SQLパズル

任意の文字列が、AaＡａのいずれかを含んでいるかチェックする。

SQL

--■■■正規表現を使う方法(10g以降)■■■
select Val,
case when RegExp_Like(Val,'[aAａＡ]') then 1 else 0 end as IsMatch
from (select 'oracle' as Val from dual
union select 'Oracle' from dual
union select 'ORACLE' from dual
union select 'ＯＲＡＣＬＥ' from dual
union select 'ｏｒａｃｌｅ' from dual
union select 'ｔｉｇｅｒ' from dual
union select 'ｗｅｎｄｙ' from dual);

--■■■Lower関数とto_single_byte関数を使う方法■■■
select Val,
case when Lower(to_single_byte(Val)) LIKE '%a%' then 1 else 0 end as IsMatch
from (select 'oracle' as Val from dual
union select 'Oracle' from dual
union select 'ORACLE' from dual
union select 'ＯＲＡＣＬＥ' from dual
union select 'ｏｒａｃｌｅ' from dual
union select 'ｔｉｇｅｒ' from dual
union select 'ｗｅｎｄｙ' from dual);

解説

Lower関数とto_single_byte関数を組み合わせると、
大文字、小文字、全角、半角を無視した英数字の比較ができます。

---