忘れた頃のSQLリファレンス STRING_SPLIT

はじめに

STRING_SPLIT()を使用して文字列を特定の区切り文字で分割し、複数行に展開する方法です。
例えば、CSV形式のデータを取り扱う際や、ユーザー入力の解析を行う場合です。

STRING_SPLIT()の使用例

基本的な文字列の分割

STRING_SPLIT関数は、指定した区切り文字で文字列を分割し、各部分を行として返します。
関数は分割対象の文字列と区切り文字の2つのパラメータを取ります。

文字列をカンマで分割する

SELECT value FROM STRING_SPLIT('apple,banana,cherry', ',');

value
-------------------
apple
banana
cherry

(3 行処理されました)

テーブル内のデータを分割する

次に、テーブル内のカンマ区切りデータを分割する方法を紹介します。

テーブルデータを空白で分割する方法

SELECT hid, value AS Hobby
FROM
	(VALUES (1, '映画鑑賞 読書 写真撮影'),(2, '登山 釣り キャンプ')
	) as Participants (hid ,Hobbies)
CROSS APPLY STRING_SPLIT(Hobbies, ' ');

hid  Hobby
--- ----------
1   映画鑑賞
1   読書
1   写真撮影
2   登山
2   釣り
2   キャンプ

(6 行処理されました)

NULLや空白の処理

STRING_SPLIT関数は、空白やNULL値も処理できます。例えば、区切り文字が連続している場合には空の部分文字列が生成されますが、それらを除外することも可能です。

分割した項目を条件で取得する

SELECT value
FROM STRING_SPLIT('clothing,road,,touing,bike', ',') --"road"と"touing"の間に空白行
WHERE value <> ''; --この条件で空白除去

value
----------
clothing
road
touring
bike

おわりに

画面からスペース区切りのテキストを持ってきて検索する機能を作るときに便利です。

SQLServerならそのままコピペしても動くように書きました。
必要に応じてテーブルを書き換えてつかってください。