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

カンマ区切りの文字列からテーブルを生成するには？

プログラムからデータベースに複数のID等を一度に渡したい時ありますよね。

ユーザー定義のテーブルを作って一度に渡すこともできますが、今回はプログラム側で ID をカンマ区切りの文字列にしてデータベースに渡し、そのカンマ区切りの文字列をスプリットして、以下のように ID を行として持つテーブルを生成したいとします。

カンマ区切りの文字列からテーブルを生成する 1

カンマ区切りの文字列からテーブルを生成する方法な何通りかありますが、今回は比較的パフォーマンスの良い単純な WHILE ループを使った方法と、スクリプトが短い XML を使った方法を見てみましょう。


WHILE ループを使ってカンマ区切りの文字列からテーブルを生成する

WHILE ループを使って、カンマ区切りの文字列からテーブルを生成するユーザー定義ファンクションは以下の通りです。 

CREATE FUNCTION dbo.ufnSplitString_WHILE(
	@Input AS NVARCHAR(MAX)
)  
RETURNS @Output TABLE(Value NVARCHAR(MAX))  
AS  
BEGIN  

	IF @Input IS NOT NULL
	BEGIN

		DECLARE @Start	INT = 1,
			@End	INT = CHARINDEX(',', @Input),
			@Len	INT = LEN(@Input);

		WHILE @End > 0
		BEGIN 
       
			INSERT INTO @Output (Value)
			   VALUES(SUBSTRING(@Input, @Start, @End - @Start));
        
			SELECT	@Start = @End + 1,
				@End = CHARINDEX(',', @Input, @Start);

		END

		INSERT INTO @Output (Value)
		   VALUES(SUBSTRING(@Input, @Start, @Len + 1 - @Start));

	END

	RETURN;
	
END

WHILEでループしながら、CHARINDEX でカンマの位置を特定して、値の Start と End の位置を取得し、SUBSTRING ファンクションで値を取り出しています。

NULL や空白文字列が入った時にどう動くかはお好みで調整してください。今回のサンプルスクリプトは、一応、 XML 版と同じ結果になるようにしてあります。


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

XML を使ってカンマ区切りの文字列からテーブルを生成する

XML のメソッドを利用して、カンマ区切りの文字列からテーブルを生成するユーザー定義ファンクションは以下の通りです。 

CREATE FUNCTION dbo.ufnSplitString_XML (
	@Input AS NVARCHAR(MAX)
)  
RETURNS @Output TABLE(Value NVARCHAR(MAX))  
AS  
BEGIN

	DECLARE @Xml XML = CAST('<val>' 
				+ REPLACE(@Input, ',', '</val><val>') 
				+ '</val>' AS XML);

	INSERT INTO @Output (Value)
	   SELECT  Col.value('.', 'NVARCHAR(MAX)')
	   FROM	   @Xml.nodes('/val') AS T(Col);

	RETURN;
	
END

まずカンマ区切りの文字列にタグを挿入して、<val>値１</val><val>値２</val><val>...というような XML を生成し、XMLの nodesとvalueメソッドを利用して、タグで囲まれた値を取り出しています。

この方法は入力文字列にタグが入っている場合は使えませんのでご注意ください。


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

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

詳しくはこちらのページをご覧ください。
「 STRING_SPLIT 関数- カンマ区切りの文字列からテーブルを生成する 2 」

© 2010-2024 SQL Server 入門