【Firebird2.5】全角・半角を問わずLIKE検索を行うことはできますか？

Question

実現したいこと

Firebird2.5 を使った Windows アプリケーションを開発しているのですが、
全角・半角を問わずカタカナを検索、なおかつ部分一致で検索することは可能でしょうか？

例えば「テスト」で検索した場合、「テスト」と「ﾃｽﾄ」の両方を抽出したいです。
「てすと」「デズド」などは抽出してもしなくても構いません。

Collation を試しましたが上手くいきません。
ご教授いただけますと幸いです。よろしくお願いいたします。

試したことと、その結果

Collation を利用しようと思い、以下のSQLで試してみました。

create database 'test.gdb' default character set UTF8;

alter character set UTF8 set default collation UNICODE_CI_AI;

create table person (
 id integer,
 name varchar(50)
);

insert into person values (1, 'テスト');

insert into person values (2, 'ﾃｽﾄ');

select * from person where name = 'テスト';      -- (A)

select * from person where name like '%テスト%'; -- (B)

(A) では想定通り、id=1, id=2 の両方が抽出できました。

しかし、(B) では id=1 しか抽出できませんでした。

バージョン情報

Firebird2.5 (32bit版, Classic Server binary)

参考にしたURL

• https://firebirdsql.org/refdocs/langrefupd25-collations.html
• https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-collation.html
• http://tech.firebird.gr.jp/firebird/site_rsrc/images/0/rlsnotes25.pdf

Answer 1

Firebirdは触ったことも無いので、外していたらすみません。
以下ではどうでしょうか。

select * from person where name collate UNICODE_CI_AI like '%テスト%'

追記

よく考えてみると大文字/小文字の識別が無いだけで全角/半角は同じにはならない気もします。
以下ではどうでしょうか。

select * from person where upper(name) like '%テスト%'

Comments

[shinoharat]

ご回答いただきありがとうございます。
こちらの方法も試してみたのですが、やはり like だと "ﾃｽﾄ" を抽出してくれませんでした。

[sazi]

UNICODE_CI_AI　以外も試されましたか？

[shinoharat]

色々とありがとうございます。

下記ドキュメントを参考に、UTF8で使えるcollation（UCS_BASIC, UNICODE, UNICODE_CI, UNICODE_CI_AI）をすべて試してみました。

https://firebirdsql.org/refdocs/langrefupd25-collations.html

また、教えていただいたupper関数も使用してみました。

```sql
--------------------------------------
-- 完全一致のみ抽出する場合
--------------------------------------

select * from person where name collate UCS_BASIC = 'テスト'; -- NG。「テスト」のみ。
select * from person where name collate UNICODE = 'テスト'; -- NG。「テスト」のみ。
select * from person where name collate UNICODE_CI = 'テスト'; -- OK。「テスト」「ﾃｽﾄ」を抽出。
select * from person where name collate UNICODE_CI_AI = 'テスト'; -- OK。「テスト」「ﾃｽﾄ」を抽出。

--------------------------------------
-- LIKE を使用した場合
--------------------------------------

select * from person where name collate UCS_BASIC like 'テスト'; -- NG。「テスト」のみ。
select * from person where name collate UNICODE like 'テスト'; -- NG。「テスト」のみ。
select * from person where name collate UNICODE_CI like 'テスト'; -- NG。「テスト」のみ。
select * from person where name collate UNICODE_CI_AI like 'テスト'; -- NG。「テスト」のみ。

--------------------------------------
-- LIKE + upper を使用した場合
--------------------------------------

select * from person where upper(name) collate UCS_BASIC like 'テスト'; -- NG。「テスト」のみ。
select * from person where upper(name) collate UNICODE like 'テスト'; -- NG。「テスト」のみ。
select * from person where upper(name) collate UNICODE_CI like 'テスト'; -- NG。「テスト」のみ。
select * from person where upper(name) collate UNICODE_CI_AI like 'テスト'; -- NG。「テスト」のみ。
```

結果としては、やはり「likeを使うと上手くいかない」状態です。

[sazi]

like で使用している文字そのものの文字コードがどうなのかという事も考えられないでしょうか
適当なテーブルを使用して、
select * from (select 'ﾃｽﾄ' as test_column from テーブル)
where test_column like '%テスト%'
などとして、状況を確認されてみては如何でしょうか。

[shinoharat]

度々アドバイスいただき、本当にありがとうございます。

select * from (select 'ﾃｽﾄ' test from rdb$database)
where test collate UNICODE_CI = 'テスト';

だと「ﾃｽﾄ」が抽出されましたが、

select * from (select 'ﾃｽﾄ' test from rdb$database)
where test collate UNICODE_CI like '%テスト%';

だと null が返ってきました。

[sazi]

select * from (select 'ﾃｽﾄ' test from rdb$database)
where test collate UNICODE_CI like '%ﾃｽﾄ%';
とか
select * from (select 'テスト' test from rdb$database)
where test collate UNICODE_CI like '%ﾃｽﾄ%';
ではどうでしょう

[sazi]

select * from (select upper('ﾃｽﾄ') test from rdb$database)
where test like '%テスト%';
も、試してください。

[shinoharat]

... 'ﾃｽﾄ' ... like '%ﾃｽﾄ%' -- 抽出成功

... 'テスト' ... like '%ﾃｽﾄ%' -- 抽出失敗

... upper('ﾃｽﾄ') ... like '%テスト%' -- 抽出失敗

となりました。

[sazi]

upperは全角変換はしてくれていないみたいですね。
利用できるような記事を見つけたので提案したのですが、他のDBMSでもそんな都合のいい関数は無いので。
原因の特定はひとまず置いておいて対応するなら、半角を全角に変換するユーザー定義関数を作成するかですね。
※translate()を使用すればそれほど大変ではないと思います。

[shinoharat]

ありがとうございます。

半角ｶﾅを全角カナに変換するユーザー定義関数（UDF）を作成し、

select * from person where my_upper_func(name) like '%テスト%'

という形で実装することにします。

色々とアドバイスいただき、本当にありがとうございました。