SQL Server 入門 SQL Server 入門

ホーム > 便利なT-SQL&クエリー集 > STRING_SPLIT 関数- カンマ区切りの文字列からテーブルを生成する 2

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, ',');

[ 実行結果 ]
STRING_SPLIT 関数- カンマ区切りの文字列からテーブルを生成する 1

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

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


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

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

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

これをカンマ区切りのカラムの 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, ','); 

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

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

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

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

SQL Server 関連の人気書籍
ホーム > 便利なT-SQL&クエリー集 > STRING_SPLIT 関数- カンマ区切りの文字列からテーブルを生成する 2