Split()-like string-to-array function in Transact-SQL
Transact-SQL doesn't have arrays or nifty string functions like the C
SELECT * FROM table1 WHERE id in (1,2,3,526);
so why no looping through comma-delimited strings?
A "Table-valued User-defined Function" returning a table variable is a handy and versatile workaround letting you query and loop through the values with near array-like ease.
The code could look like this:
CREATE FUNCTION [dbo].[StringToTable] ( @inputString nvarchar(max), @separator char (1) ) RETURNS @ResultTable TABLE ( [String] nvarchar(max) ) AS BEGIN DECLARE @stringToInsert nvarchar (max) WHILE LEN(@inputString) > 0 BEGIN SET @StringToInsert = LEFT( @inputString, ISNULL(NULLIF(CHARINDEX(@separator, @inputString) - 1, -1), LEN(@inputString) ) ) SET @InputString = SUBSTRING(@InputString, ISNULL (NULLIF (CHARINDEX(@separator, @InputString), 0), LEN(@InputString)) + 1, LEN(@InputString)) INSERT INTO @ResultTable ( [String] ) VALUES ( @StringToInsert ) END RETURN END
Leo: Man, thank you very much for this. I was looking so hard for something like this and I found your site. Thanx a lot Leo
Ash: Nice post, I was looking for this SQL function today. Thanks. Ash
BlueFoot: Thank you so much for this function, helped out so much. Normally it wouldn't be useful as a good db wont have a comma list in one of the columns or values, but you run across it sometimes... thank!
Praveen: Here is the same but slightly different version. http://praveenbattula.blogspot.com/2010/06/best-split-udf-function-with-delimeter.html
George: Thanks for the great read. It was exactly what I was looking for.
Biljana: Thanks a lot, exactly what i needed :)
Franz: Thanky also. Did this several times with hardcoded fieldnames and ID's etc. Now it's streamline.
Posted: 07 May 2007