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:
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