STRING_SPLIT 関数- カンマ区切りの文字列からテーブルを生成する 2

便利な STRING_SPLIT 関数

以前、「カンマ区切りの文字列からテーブルを生成する」で、WHILE や XML を使って、カンマ区切りの文字列からテーブルを生成するユーザー定義関数を作りました。

SQL Server 2016 (互換性レベル 130 以上) からは、 STRING_SPLIT という組み込み関数が使えるようになり、自前でユーザー定義関数を作る必要がなくなりました。

※ SQL Server 2016 より前、もしくは、SQL Server 2016 以降で互換性レベル 130 未満で使っている方はこちらをご覧ください。
「カンマ区切りの文字列からテーブルを生成する」

STRING_SPLIT 関数

STRING_SPLIT は、第一引数の文字列を、第二引数で指定した区切り文字で分割して、結果をテーブルとして返してくれる関数です。

STRING_SPLIT 関数の構文は次の通りです。

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

[ 区切り文字 ] は 1 文字で指定します。

結果は「 value 」という名前のカラムをひとつだけ持つテーブル型で返ってきます。

例えば、'56,34,128,62' という文字列をカンマでスプリットすると次のようになります。

DECLARE @Input NVARCHAR(MAX) = '56,34,128,62';

SELECT * FROM STRING_SPLIT(@Input, ',');

[ 実行結果 ]

ちなみに空文字列や NULL が入力値になった時は次のようなテーブルが返ってきます。

また、CROSS APPLY や OUTER APPLY を使って、カンマ区切りの値を持つカラムを元に、行を分解することもできます。言葉ではわかりにくいかと思いますので、以下に例をあげておきます。

次のような Student テーブルがあるとします。本来は StudentID と TestID の関係を持つ別のテーブルを作ったほうが良いかと思いますが、TestIDList にその人が受けるテストの TestID をカンマ区切りで保存しているとします。

これをカンマ区切りのカラムの TestIDList が NULL の時は、Student の行自体を返さなくてよい場合 (INNER JOIN をしたような感じ) は CROSS APPLY を、TestIDList が NULL の時もStudent の行を結果として返したい場合 (LEFT JOIN をしたような感じ）は OUTER APPLY を STRING_SPLIT 関数と共に使うと次のような結果セットが得られます。

SELECT  * 
FROM    Student AS S
            CROSS APPLY STRING_SPLIT(TestIDList, ',');

SELECT  * 
FROM    Student AS S
            OUTER APPLY STRING_SPLIT(TestIDList, ',');

スプリット用のユーザー定義関数を作らなくてもいいですし、便利ですね！