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

プログラミング SQL Server

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

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

スポンサーリンク

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

---------------------------------------------
--最後の1つが残るのでここで追加する
---------------------------------------------
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', ',')

上記の呼び出し結果は

idx stirng
1 aaa
2 bbb
3 ccc
4 ddd
5 eee

になります。

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

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

タイトルとURLをコピーしました