SQL色々

メモ SQL

最近、SQLを色々と書いてるので、メモ。環境は、CentOS, PostgreSQL8,UTF8。

■その1
複数のカラム(hogecol1,hogecol2)から、ある文字列(keyword)が含まれるレコードを抽出する。

select * from tablename where hogecol1 like '%keyword%' or hogecol2 like '%keyword%';

上記のSQLで用件は満たせるけど、検索対象のカラムが100個とかあると、where以下を記述するだけで大変。
そこでもっとスマートに解決できる方法はないかと調査中。誰か知ってる人がいれば教えてください。

  • 追記

とりあえず、下記のように対応することにしました。

select * from tablename where (hogecol1 || hogecol2) like '%keyword%';


上記の対応は不適切なことが分かりました。(hogecol1 || hogecol2)だと、hogecol1にaaa, hogecol2にbbbという値が入っていた場合、keywordにaabbという値をいれてもヒットしてしまうため。上記の例だと、aaabbbという値に対して、keywordでlikeをかけているということなので。ということで、素直に

select * from tablename where hogecol1 like '%keyword%' or hogecol2 like '%keyword%';

という対応にしました。


■その2
あるカラム(hogecol)の中でア行で始まる値を検索したい時は、こんな感じ

SELECT * FROM tablename WHERE substr(hogecol,1,1) between 'ァ' and 'オ';

substrで1文字目を対象にして、betweenで検索文字の範囲を指定。UTF8だと、ここ（http://ash.jp/code/unitbl21.htm）にあるように、カタカナの文字コードは、アから順に並んでいるので、betweenで範囲指定が可能。

■その3
複数の検索結果を、レコード重複させて表示させたい場合は、union allを利用。重複させたくない場合はunionを利用。
例えば、あるレコードの主キーとなるidカラムに1、地域1(hogecol1)のカラムに「アタミ」、地域2(hogecol2)というカラムに「イトウ」の値が入っていて、地域1、地域2をそれぞれア行の検索をして、ヒットしたものを地域カラムとして表示。その際に、同一レコードで地域１にも地域２にもヒットした場合は、それぞれ別レコードとして表示する。そんな例の場合（分かりづらい例だな・・・）

select id,chiiki from (
select id,hogecol1 as chiiki from tablename where substr(hogecol1,1,1) between 'ァ' and 'オ'
union all
select id,hogecol2 as chiiki from tablename where substr(hogecol2,1,1) between 'ァ' and 'オ'
) as hogetable;

上記のSQLを投げれば、
1 アタミ
1 イトウ
のように表示される。