Search number using like from string in mysql

Question

Field value - 29,21,30,28,32,35,49,50,137

SELECT *
FROM `smartspace_pickups`
WHERE `cat_id` LIKE '%37%'    
LIMIT 0 , 30

I have field in which the ids are stored comma separated now i tried to fetch data using the above query but it giving me the values with the ID 137 and 371. I only want the value with the id 37 in the string.

BlitZ · Accepted Answer · 2013-04-16 11:39:11Z

2

SELECT *
FROM `smartspace_pickups`
WHERE FIND_IN_SET('37',`cat_id`)
LIMIT 0 , 30

answered Apr 16, 2013 at 11:39

BlitZ

12.2k3 gold badges52 silver badges70 bronze badges

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

Comments

Rishabh · Accepted Answer · 2013-04-16 11:39:38Z

2

you can use FIND_IN_SET(str,strlist)

SELECT *
FROM `smartspace_pickups`
WHERE FIND_IN_SET('37', `cat_id`)  > 0
LIMIT 0 , 30

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

answered Apr 16, 2013 at 11:39

Rishabh

1,2151 gold badge13 silver badges28 bronze badges

Comments

Nauphal · Accepted Answer · 2013-04-16 11:45:34Z

0

make your ids field like this (add comma to the starting and ending)

,29,21,30,28,32,35,49,50,137,

and change your query to

LIKE '%,37,%'

answered Apr 16, 2013 at 11:45

Nauphal

6,1824 gold badges30 silver badges43 bronze badges

3 Comments

BlitZ Over a year ago

And if there is only one value in field?

Nauphal Over a year ago

@CORRUPT: will be like ,1,

BlitZ Over a year ago

Then WHERE CONCAT(',', cat_id, ',') LIKE '%,37,%' should work. Yea)

Collectives™ on Stack Overflow

Search number using like from string in mysql

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related