Convert String to Table/Array Script

This function splits a string by a specified delimiter and returns the separated values as a table or array. The default separator is the pipe (|) symbol.

Author: R. Rasu

/****** Object:  User Defined Function dbo.fn_SplitIn2Rows    
Script Date: 8/10/2002 6:43:52 PM ******/
CREATE FUNCTION fn_SplitIn2Rows
    (
	  @PInStrSource varchar(8000) = NULL, 
      @pInChrSeparator char(1) = '|'
	)
	RETURNS 
		@ARRAY TABLE (ItemValue VARCHAR(1000))
AS
BEGIN
	DECLARE @CurrentStr varchar(2000)
	DECLARE @ItemStr varchar(200)
	
	SET @CurrentStr = @PInStrSource
	 
	WHILE Datalength(@CurrentStr) > 0
	BEGIN
		IF CHARINDEX(@pInChrSeparator, @CurrentStr,1) > 0 
			BEGIN
	           	SET @ItemStr = SUBSTRING (@CurrentStr, 1, CHARINDEX(@pInChrSeparator, @CurrentStr,1) - 1)
	            SET @CurrentStr = SUBSTRING (@CurrentStr, CHARINDEX(@pInChrSeparator, @CurrentStr,1) + 1, (Datalength(@CurrentStr) - CHARINDEX(@pInChrSeparator, @CurrentStr,1) + 1))
				INSERT @ARRAY (ItemValue) VALUES (@ItemStr)
	   		END
		 ELSE
		 	BEGIN                
				INSERT @ARRAY (ItemValue) VALUES (@CurrentStr)	 		
	           	BREAK;
	       	 END 
	END
	RETURN
END

--THE USAGE: 
--SELECT * FROM Stores WHERE
--		StoreId IN 
--		(
--			SELECT ItemValue FROM fn_SplitIn2Rows('44|23|11|4|89|21', '|')
--		)

Disclaimer: We hope that the information on these script pages is
valuable to you. Your use of the information contained in these pages,
however, is at your sole risk. All information on these pages is provided
"as -is", without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose...

Disclaimer Continued

Back to Database Journal Home