Function to return a delimited list as a table

,

This SQL2000 function accepts a delimited list of values as a string and the character you want to use as a delimiter.

The function then splits these out and returns them as a table.

The function assumes that the input string will contain unique integer values.

Useage: -  SELECT Id FROM fnSplit('1,2,3,4' , ',')

CREATE FUNCTION fnSplit ( @sInputString VARCHAR(8000) , @sSplitChar CHAR(1))
	RETURNS @tbl_List TABLE (Id Int PRIMARY KEY)

AS
	BEGIN
		DECLARE	@lInputStringLength	Int ,
			@lPosition		Int ,
			@lSplitChar		Int 


		SET	@lInputStringLength = LEN ( @sInputString )
		SET 	@lPosition=1
		SET	@lSplitChar=1
	
	
		WHILE @lPosition <= @lInputStringLength
			BEGIN
				SET @lSplitChar = CHARINDEX ( @sSplitChar , @sInputString , @lPosition)
				IF @lSplitChar = 0
					BEGIN
						INSERT @tbl_List ( Id )
						SELECT CAST( SUBSTRING( @sInputString , @lPosition ,1+ @lInputStringLength - @lPosition) AS Int )
						SET @lPosition= @lInputStringLength + 1
					END
		
				ELSE
					BEGIN
						INSERT @tbl_List ( Id )
						SELECT CAST ( SUBSTRING( @sInputString , @lPosition , @lSplitChar - @lPosition) AS INT )
						SET @lPosition = @lSplitChar+1
					END
			END
	

		RETURN
	END

Rate

Share

Share

Rate