- PR -

PostgreSQL8.1でのパターンマッチング

1
投稿者投稿内容
s15spr
会議室デビュー日: 2006/01/17
投稿数: 10
投稿日時: 2006-01-17 10:01
お世話になります。

環境：OS Windows2000 Server SP4
DB PostgreSQL 8.1 日本語版

現在PostgreSQL8.1を利用してシステム構築をしていますが、
テーブル検索時のパターンマッチングで困っています。

あるテーブルの日本語データ（半角全角、大文字小文字が混在する）を検索したいのですが、
SQLServerのように半角全角、大文字小文字区別無く検索することはできないのでしょうか？

例：TEST_TBL　　
　　ID,TEXTDATA
　　1.０１234
　　2.tEst
3.山田ﾀﾛｳ

Select * From TEST_TBL Where TEST_TBL Like '%0%'
結果：1.０１234

Select * From TEST_TBL Where TEST_TBL Like '%e%'
結果：2.tEst

Select * From TEST_TBL Where TEST_TBL Like '%タ%'
結果：3.山田ﾀﾛｳ

どなたかご教授お願い出来ないでしょうか？
今川 美保(夏椰)
ぬし
会議室デビュー日: 2004/06/10
投稿数: 363
お住まい・勤務地: 神奈川県茅ヶ崎市
投稿日時: 2006-01-17 10:59
う〜ん。汚いSQLになるかもしれません。

ローマ字を小文字→大文字にするにはUPPER関数が使えますよね。
(逆に小文字→大文字ならLOWER)

日本語の半角→全角ならTranslateを使ってしまうぐらいしか思いつかないですね、自分には。
コード:
  translate('ｱ','ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾜｦﾝァィゥェォャュョヮぁぃぅぇぉゃゅょゎ','アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨワヲンぁぃぅぇぉゃゅょゎあいうえおかやゆよわ')


まとめるとこんな感じ・・・かな。
コード:
select 
  col1,
  col2,
  upper(col1),
  upper(col2),
  translate(upper(col1),'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾜｦﾝァィゥェォャュョヮぁぃぅぇぉゃゅょゎ','アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨワヲンぁぃぅぇぉゃゅょゎあいうえおかやゆよわ'),
  translate(upper('ｲ'),'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾜｦﾝァィゥェォャュョヮぁぃぅぇぉゃゅょゎ','アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨワヲンぁぃぅぇぉゃゅょゎあいうえおかやゆよわ')
from test 
where translate(upper(col1),'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾜｦﾝァィゥェォャュョヮぁぃぅぇぉゃゅょゎ','アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨワヲンぁぃぅぇぉゃゅょゎあいうえおかやゆよわ') ~ 
      translate(upper('ｲ'),'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾜｦﾝァィゥェォャュョヮぁぃぅぇぉゃゅょゎ','アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨワヲンぁぃぅぇぉゃゅょゎあいうえおかやゆよわ')
;


で先ほど大文字・小文字を変換する関数(UPPER/LOWER)をあげたんですが、
PostgreSQLでのパターンマッチで~を~*で書くと大文字・小文字の区別なく
判断してくれるので、こう書き直しても同じになります。
コード:
select 
  col1,
  col2,
  upper(col1),
  upper(col2),
  translate(upper(col2),'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾜｦﾝァィゥェォャュョヮぁぃぅぇぉゃゅょゎ','アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨワヲンぁぃぅぇぉゃゅょゎあいうえおかやゆよわ'),
  translate(upper('a'),'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾜｦﾝァィゥェォャュョヮぁぃぅぇぉゃゅょゎ','アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨワヲンぁぃぅぇぉゃゅょゎあいうえおかやゆよわ')
from test 
where translate(col2,'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾜｦﾝァィゥェォャュョヮぁぃぅぇぉゃゅょゎ','アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨワヲンぁぃぅぇぉゃゅょゎあいうえおかやゆよわ')
       ~* 
      translate('a','ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾜｦﾝァィゥェォャュョヮぁぃぅぇぉゃゅょゎ','アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨワヲンぁぃぅぇぉゃゅょゎあいうえおかやゆよわ')
;


もっといいやり方が思いつけばいいんですけどね。
とりあえず実現できなくはないのかなぁ？と思って書いてみました。
s15spr
会議室デビュー日: 2006/01/17
投稿数: 10
投稿日時: 2006-01-17 13:18
夏椰(冒険者)さんありがとうございます。

ご回答ありがとうございました。

この方法で頑張ってみます。

なにぶん、PostgreSQLが初めてなもので分からないことばかりです。

後々SQLServerへ移行することが検討されているため、そういった部分に見劣りが
無いように作る必要があったので助かります。

ありがとうございました。
鎌田
常連さん
会議室デビュー日: 2003/09/23
投稿数: 45
投稿日時: 2006-01-17 13:24
夏椰さんのSQLを関数にしておくと使いやすいでしょう。

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

※カナ変換部分は表記を短くするために省略。

こんな感じで。
さらに頻繁に使用するなら、式インデックスを作成します。

CREATE INDEX test_tbl_textdata ON test_tbl (get_nocase_text(textdata));

これで毎回式を評価する必要がなくなりますし、LIKEの前方一致時にこのインデックスが効く場合もでてきます。

SELECT * FROM test_tbl
WHERE get_nocase_text(textdata) LIKE 'Ａ４%';
s15spr
会議室デビュー日: 2006/01/17
投稿数: 10
投稿日時: 2006-01-17 14:38
夏椰(冒険者)さん、鎌田さんありがとうございます。

実際にやってみましたが、こちらが想定している結果となり安心しました。

関数自体は敷居が高いと思っていましたが、意外に出来るものですね。
驚きました。

ご教授、ありがとうございます。
鎌田
常連さん
会議室デビュー日: 2003/09/23
投稿数: 45
投稿日時: 2006-01-18 16:06
もうみていないかも知れませんが、半角カタカナの濁点と半濁点はどう処理しました？
PL/Perlなどで変換関数を作成されたのでしょうか。
1

スキルアップ／キャリアアップ（JOB@IT）