SQL Server や T-SQL を初めて学ぶ人のための学習サイトです。 便利なクエリーやチューニングの仕方なども掲載しています。
SQL Server 入門
T-SQL 入門
便利なT-SQL&クエリー集
SQL Server&T-SQLに関するコラム
サイト情報
MCTS: SQL Server 2008, Database Development
サイト内検索
カスタム検索
ホーム > 便利なT-SQL&クエリー集 > カンマ区切りの文字列からテーブルを生成する

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

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

プログラムからデータベースに複数の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>値1</val><val>値2</val><val>...というような XML を生成し、XMLの nodesとvalueメソッドを利用して、タグで囲まれた値を取り出しています。

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


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


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

© 2014 SQL Server 入門