CODEWITHSUNDEEP
mysql
Hard worker
Hard worker

Reputation: 4046

How to query comma delimited field of table to see if number is within the field

I want to select rows from a table if a column contains a certain number e.g 981. The data in these columns is either 0, null or in the format below:

1007,1035,1189,908,977,974,979,973,982,981,1007

How do I phrase this in a query?

Obvious this query below isn't sufficient but I need something similar

 SELECT * FROM `table` WHERE `column`='981'

Thanks

Upvotes: 0

Views: 2056

Answers (2)

sikander
sikander

Reputation: 2286

You can use the IN clause which checks whether a value is within a set of values.

SELECT * FROM `table` WHERE `column` IN (1007,1035,1189,979,973,982,981,1007)

or

SELECT * FROM `table` WHERE `column` IN ('abc','def','ghi')

Please note that cannot mix quoted and unquoted values in the IN list

EDIT

I misunderstood the original question. If your column data is like 1007,1035,1189,979,973,982,981,1007 and you're searching for the presence of 981 then you'll have to use LIKE instead of IN

SELECT * FROM table WHERE (column LIKE ('%,981,%') OR column LIKE ('%,981') OR column LIKE ('981,%'))

to pattern match the value 981 in the middle, at the end or at the beginning of those comma separated values.

Upvotes: 2

Krzysiek Grzembski
Krzysiek Grzembski

Reputation: 994

If you have regular expressions it may work:

SELECT * FROM `table` WHERE `column` REGEXP  '(^|,)951($|,)'

Upvotes: 3

Related Questions