読者です 読者をやめる 読者になる 読者になる

ソフトウェア開発者の日常

こだわりなく書きたいことを書いていきます。

PostgreSQL:特定の区切り文字で連結されて1カラムに入っているデータを、分割して複数のレコードとして取得する方法

PostgreSQL SQL
スポンサーリンク

既にデータベースに登録されているデータを元にして、新しいデータを作成しなければならなくなりました。

元のデータを見ると、以下の表の様に特定の区切り文字(/)で連結されています。

番号(no) 名前(name)
1 大沼 直輝
2 金谷 凌
3 青井 敬三/表 博康
4 八尾 功二
5 春日井 伸次/鴨川 克成/川平 将大
6 有沢 竜平/神埼 修吾
注:名前はジェネレータでランダム生成しています。

名前で分けてデータを作成しなければいけないので、データを取り出して、Excel上でがんばって加工するしかないのかと思いましたが、時間もかかるし、間違えそうです。

PHPでデータを取得して加工することも考えましたが、プログラムの作成とデバッグに時間はかけたくありません。

f:id:AJYA:20170316225149j:plain
photo credit: tomasvondra DSC05152 via photopin (license)

クエリで対処する方法

クエリで対処できないかと考えて、以下のクエリを考えました。

SELECT * FROM 
(
  SELECT SPLIT_PART(name ,'/', 1) AS name2, * FROM data_table
  UNION
  SELECT SPLIT_PART(name ,'/', 2) AS name2, * FROM data_table
  UNION
  SELECT SPLIT_PART(name ,'/', 3) AS name2, * FROM data_table
) AS tmp
WHERE name2 <> '' ORDER BY no; 

PostgreSQLの文字列関数SPLIT_PARTを使って、特定の区切り文字(/)で文字列を分割して、指定したフィールドを取得しています。
指定したフィールドがなければ空文字になるので、空文字以外を取得しています。
これで考えたとおりのデータが取得できました。