Wednesday, 22 June 2011

Using comma-delimited lists in SQL searches

Today I wanted to run a search against a comma-delimited list of values, a little like this:
Select name from People Where category IN ('N,S,L,C')
I wanted the comma-delimited list to be set as a parameter value in a stored procedure. However, the IN operator cannot accept a comma-delimited string. It only accepts comma-delimited constants or the results of a SQL query.

Eventually I found this excellent solution on the 4 Guys site. It shows you how to build a UDF (user-defined function) that will split a comma-delimted string into a recordset representation, and then how to make use of it. It's a neat and effective solution.

No comments:

Post a Comment

Comments are moderated, so you'll have to wait a little bit before they appear!