カラム内の区切り文字でデータを分割して取得する方法（SQLServer）。

• facebook

オフィス狛 技術部のHammarです。

ある開発で、テーブルのカラムにカンマ区切りで値が入っているデータがあるのですが、ここからカンマ区切りで1つのレコードとして分割してデータ抽出したいと要望がありました。
取得イメージ的には、例えば以下のようなfruits_boxテーブルがあったとします。

+----+-------+-----------------------+
| id | name  | fruits                |
+----+-------+-----------------------+
|  1 | boxA  | apple,grape           |
|  2 | boxB  | apple,orange,banana   |
|  3 | boxC  | banana                |
+----+-------+-----------------------+

こちらから

+----+-------+-----------------------+
| id | name  | fruits                |
+----+-------+-----------------------+
|  1 | boxA  | apple                 |
|  1 | boxA  | grape                 |
|  2 | boxB  | apple                 |
|  2 | boxB  | orange                |
|  2 | boxB  | banana                |
|  3 | boxC  | banana                |
+----+-------+-----------------------+

のように取得する感じです。

カラム内にカンマ区切りでデータを入れることは結構あると思います。ただ、そのデータをレコードとして分割して出力することがなかったのですが、今回調べてみて意外と簡単な方法があったのでちょっと書いてみたいと思います。 RDBMSによってそれぞれ書き方は異なるようですが、開発でSQLServerを使っていたので、今回はそのSQLServerで上記のような構成でデータを取得する方法となります。

■STRING_SPLIT関数

まずSQLserverで、区切り文字で分割して結果を取得する方法としては、STRING_SPLIT関数を使う方法があります。
STRING_SPLIT関数は、

STRING_SPLIT(文字列, 区切り文字)

の方法で利用することができます。
使い方としてはこんな感じです。

> SELECT * FROM STRING_SPLIT('apple,orange,banana', ',');
+-----------+
| value     |
+-----------+
| apple     |
| orange    |
| banana    |
+-----------+

これはこれで使えそうなんですが、そもそもこれを使うには1レコード対象にしか使えません。
なので、今回のような複数レコードが存在する場合にはちょっと無理そうです。

■CROSS APPLY句

ということで、もう１つの方法として、「CROSS APPLY」という結合演算子と合体技で取得する方法です。
APPLY句については今回細かく記載は省きますが、テーブルから取得したデータをテーブル値関数の結果として組み合わせて使いたい場合にこのAPPLYという演算子は有効です。

これを使ってSQLを書くと以下のように書くことが出来ます。

> SELECT * 
> FROM fruits_box AS FB
> CROSS APPLY STRING_SPLIT(fruits, ','); 
+----+-------+-----------------------+
| id | name  | fruits                |
+----+-------+-----------------------+
|  1 | boxA  | apple                 |
|  1 | boxA  | grape                 |
|  2 | boxB  | apple                 |
|  2 | boxB  | orange                |
|  2 | boxB  | banana                |
|  3 | boxC  | banana                |
+----+-------+-----------------------+

という感じで、ほしいデータの結果を得ることが出来ました。
結構簡単な記述でいけますね！

1点注意点としては、上記テーブルのfruitsにNULLがあった場合、その結果は返ってこないので、もしNULLも結果として返したい場合は、「OUTER APPLY」を使うことでNULLレコードも結果として得ることができるようになります。

もし上記のような、カラム内の区切り文字で、それぞれレコードとしてデータ取得が必要な場合にはご参考にしてみてください。

SQL Server