CODEWITHSUNDEEP
mysql
Ozone
Ozone

Reputation: 429

Compare comma-separated string in MySQL column where column is also comma separated

Compare comma-separated string in MySQL column where column is also comma separated

For Example:

Id name catid
1  abc   4,5,2
2  bcd   5
3  efg   9,1,7

SELECT * FROM TABLE WHERE catid IN (2,5,6)

Here 2,5,6 I have to compare with the catid value to get the result. Any Help to get the right out put, I used FIND_IN_SET, but could not make it work for my case

Upvotes: 0

Views: 115

Answers (2)

Strawberry
Strawberry

Reputation: 33935

Note that I'm not seriously advocating this as a solution...

SELECT * FROM my_table WHERE FIND_IN_SET(5,catid) OR FIND_IN_SET(2,catid);

Upvotes: 0

Chintan Gor
Chintan Gor

Reputation: 1072

according to your problem you can use LIKE operator instead of IN 

SELECT * FROM testchintan  WHERE CONCAT(',',catid,',') LIKE '%,5,%'
OR CONCAT(',',catid,',') LIKE '%,2,%' OR CONCAT(',',catid,',') LIKE '%,6,%'

OR YOU can use REGEXP

SELECT *
  FROM testchintan
 WHERE catid REGEXP '[4,9,5]'

OR 

SELECT *
  FROM testchintan
 WHERE 
 REPLACE(catid,',','|') REGEXP '[9,7]'

Upvotes: 0

Related Questions