How to filter Sql table on the basis of CSV column

Question

How to filter an SQL table on the basis of a VALUE that exist in its CSV column. In detail, I have a table that has a CSV column containing integer number in comma separated form like

12,234,32,55 
121,64,43,65
54,25,112,12
996,612,55,3  etc.

now from this table I want to find out the rows that has the value 12(twelve) in csv Column i.e., the row with csv record

12,234,32,55
54,25,112,12 only

Please Help

It would be better to redesign your schema so that each value was stored in a separate row. You're not meant to stuff multiple values into a single column. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Feb 7, 2013 at 7:20

bummi · Accepted Answer · 2013-02-07 07:16:06Z

1

Select *
from Tablex 
where 
(Col like '%,12,%') 
 or (Col = '12')
 or LEFT(Col,3)='12,'
 or RIGHT(col,3)=',12'

or better as Love2Learn suggested

 Select * From Tablex Where ','+Col+',' Like '%,12,%'

edited Feb 7, 2013 at 7:16

answered Feb 7, 2013 at 6:35

bummi

27.4k14 gold badges67 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Eric J. Price Over a year ago

You can do this to simplify it... Select * From Tablex Where ','+Col+',' Like '%,12,%'

GouravJn Over a year ago

Thanks @Love2Learn, it works perfectly... Can u please post it as answer, so i can accept it... :-)

GouravJn Over a year ago

Also thanks @bummi for your answer... it works too but a bit difficult to understand for me

Eric J. Price Over a year ago

@bummi answer is more or less the same. Bummi, can you update your answer to the shortened form so OP can accept your answer?

Collectives™ on Stack Overflow

How to filter Sql table on the basis of CSV column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related