Convert Field containing comma-separated text to fields?   

Hi.  I have a column named 'Departments' which contains a comma-separated 
list of integers (i.e. - 1,2,6,10,25).

I want to write a SELECT statement where I can pass-in the integer I'm 
looking for and retrieve that record.  For example (this is just pseudo-code):

SELECT * FROM table WHERE @myinteger IN (Departments)

Is this possible? Thanks!

date: Mon, 29 Sep 2008 09:15:01 -0700   author:   Mike

Re: Convert Field containing comma-separated text to fields?   

A few different methods to split the delimited column in this article by 
Erland Sommarskog:
http://www.sommarskog.se/arrays-in-sql.html

Normally the case is with having a normalized column and passing a 
delimited list to match (which is what Erland's article concentrates on, 
but the same methods are applicable here).

You can write queries like below, but those are not reliable (as 
additional spaces between commas can have effect), and performance will 
not be good:

SELECT <columns>
FROM MyTable
WHERE ',' + departments + ',' LIKE '%,' + CAST(@myinteger AS 
VARCHAR(10)) + ',%';

SELECT <columns>
FROM MyTable
WHERE CHARINDEX(',' + CAST(@myinteger AS VARCHAR(10)) + ',',
                 ',' + departments + ',') > 0;


If possible it will be best to normalize the column, then all queries 
will be easy.

-- 
Plamen Ratchev
http://www.SQLStudio.com

date: Mon, 29 Sep 2008 12:34:50 -0400   author:   Plamen Ratchev

Re: Convert Field containing comma-separated text to fields?   

Thank you!

"Plamen Ratchev" wrote:

> A few different methods to split the delimited column in this article by 
> Erland Sommarskog:
> http://www.sommarskog.se/arrays-in-sql.html
> 
> Normally the case is with having a normalized column and passing a 
> delimited list to match (which is what Erland's article concentrates on, 
> but the same methods are applicable here).
> 
> You can write queries like below, but those are not reliable (as 
> additional spaces between commas can have effect), and performance will 
> not be good:
> 
> SELECT <columns>
> FROM MyTable
> WHERE ',' + departments + ',' LIKE '%,' + CAST(@myinteger AS 
> VARCHAR(10)) + ',%';
> 
> SELECT <columns>
> FROM MyTable
> WHERE CHARINDEX(',' + CAST(@myinteger AS VARCHAR(10)) + ',',
>                  ',' + departments + ',') > 0;
> 
> 
> If possible it will be best to normalize the column, then all queries 
> will be easy.
> 
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com
>

date: Mon, 29 Sep 2008 10:13:02 -0700   author:   Mike