SELECT WHERE IN comma delimited string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT WHERE IN comma delimited string

Author

Topic

SquareEye
Starting Member

5 Posts

Posted - 2008-06-03 : 12:10:55

Hello

I have a table with column Options where each field contains a comma delimited list of ID numbers.

I want to select any records where a certain ID number appears in that list.

So something like:

SELECT * FROM Table t1
WHERE MyID IN (SELECT Options FROM Table t2 WHERE t1.ID = t2.ID)

But that gives me the error:

Syntax error converting the varchar value '39,20' to a column of data type int.

I feel I'm close though! Could anyone point me in the right direction?

Many thanks

Square

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 12:21:33

SELECT * FROM Table t1
WHERE ',' + Option + ',' LIKE '%,' + CAST(MyID AS varchar(10)) + ',%'