SQLServerでSplit関数のようにカンマ区切りの文字列を分割して取得する方法

SQLServerではSQLServer2016以降やAzure SQL DatabaseではOracleのSPLIT()のような文字列を特定の分割文字（separator）で分割して取得する関数「STRING_SPLIT」が用意されているのですが、それ以前のバージョンのSQLServerでは用意されていません。

SQLServerのSTRING_SPLITで指定した文字で文字列を分解する方法

SQLServer2016以降では指定された文字で文字列を分解して取得するにはSTRING_SPLITを使えば簡単に行うことができます。ただし、取得されるSELECT文のカラム名はvalueに固定されています。

lightgauge.net

そこでどうしても分割する必要がある場合は自作をするしかなく、必要になったので作ってみました。

SQLServerで文字列をカンマ区切りで取得する

実際には関数を作成してSELECT文のFROM句で使えるようにしました。

また、SQLServer2016以降で準備されている「STRING_SPLIT」と同じように呼び出し側から分割するための文字（separator）を指定できるようにし、分割した順番を明確にするためIDENTITYを使って自動で連番を挿入するようにしました。

CREATE FUNCTION MySplit
(
@target_str AS VARCHAR( MAX ) --分割対象の文字列
,@separator AS VARCHAR( 1 ) --分割する文字
)
RETURNS @out_table TABLE
(
idx INT IDENTITY( 1, 1 ) --テーブル挿入インデックス（自動で連番を振る）
,stirng VARCHAR( MAX ) --分割後の文字列
)
AS
BEGIN

DECLARE @start_pos INT = 1
DECLARE @end_pos INT = CHARINDEX( @separator , @target_str )
DECLARE @str_len INT = LEN( @target_str )

---------------------------------------------
--指定された文字列が空の場合は処理を行わない
---------------------------------------------
IF @target_str = '' OR @target_str IS NULL
BEGIN
RETURN
END

--------------------------------------------------------------
--文字列内の最後の分割する文字まで分割文字を検索して分割する
--------------------------------------------------------------
WHILE @end_pos > 0
BEGIN
INSERT INTO @out_table
(
stirng
)
VALUES(
SUBSTRING( @target_str, @start_pos, @end_pos - @start_pos )
)

SET @start_pos = @end_pos + 1
SET @end_pos = CHARINDEX( @separator, @target_str, @start_pos )
END

---------------------------------------------
--最後の１つが残るのでここで追加する
---------------------------------------------
INSERT INTO @out_table
(
stirng
)
VALUES(
SUBSTRING( @target_str, @start_pos, @str_len + 1 - @start_pos )
)

---------------------------------------------
--処理終了
---------------------------------------------
RETURN

END

この関数の使い方はSELECT文などのFORM句に指定して使います。

SELECT * FROM dbo.MySplit( 'aaa,bbb,ccc,ddd,eee', ',')

上記の呼び出し結果は

になります。

分割するための文字（separator）を変えれば色んなパターンの文字列が分割できると思います。

以上、「SQLServerでSplit関数のようにカンマ区切りの文字列を分割して取得する方法」でした。