Convert String to Table/Array Script

March 12, 2004


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









The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers