PostgreSQL:特定の区切り文字で連結されて1カラムに入っているデータを、分割して複数のレコードとして取得する方法
既にデータベースに登録されているデータを元にして、新しいデータを作成しなければならなくなりました。
元のデータを見ると、以下の表の様に特定の区切り文字(/)で連結されています。
番号(no) | 名前(name) |
---|---|
1 | 大沼 直輝 |
2 | 金谷 凌 |
3 | 青井 敬三/表 博康 |
4 | 八尾 功二 |
5 | 春日井 伸次/鴨川 克成/川平 将大 |
6 | 有沢 竜平/神埼 修吾 |
名前で分けてデータを作成しなければいけないので、データを取り出して、Excel上でがんばって加工するしかないのかと思いましたが、時間もかかるし、間違えそうです。
PHPでデータを取得して加工することも考えましたが、プログラムの作成とデバッグに時間はかけたくありません。
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を使って、特定の区切り文字(/)で文字列を分割して、指定したフィールドを取得しています。
指定したフィールドがなければ空文字になるので、空文字以外を取得しています。
これで考えたとおりのデータが取得できました。