Dirk Bergmann

Developer, panographer. Does consultancy work from own company. Lives in Chiang Mai, Thailand.

Twitter

Chiang Mai

Hua Hin

Thailand Hotels

ListVote - List. Vote. Learn.

Partnerseek - Find Partners

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.