asked on

searching csv values from a single feild in mysql

I have a 'new_view' table as below:
device-----adapter-----prod_ids
HY97C51-----No-----1,2,3,4,5,6
HF97PP1-----No-----3,4,5,6
FF9LP221-----No-----5,6

the last coloumn has product ids saved in csv.

I want to get device for prod_ids = 4

I tried below queries:

SELECT * FROM `new_view` WHERE `prod_ids` LIKE '%4%'
Gives wrong output

SELECT * FROM `new_view` WHERE `prod_ids` LIKE '4%'
doesnt consider values in prodids as 1,2,3,4

SELECT * FROM `new_view` WHERE `prod_ids` LIKE '%4'
doesnt consider values in prodids as 4,5,6
FireShot-capture--082----localho.gif

ASKER CERTIFIED SOLUTION

theGhost_k8

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hielo

try:
SELECT * FROM `new_view` WHERE CONCAT(',', `prod_ids`,',') LIKE '%4%'

shaunak

ASKER

For hielo suggestions it also returns prodid with 14

Trying combination with theGhost_k8 suggestion

shaunak

ASKER

For hielo suggestions it also returns prodid with 14

Trying combination with theGhost_k8 suggestion

shaunak

ASKER

For hielo suggestions it also returns prodid with 14

Trying combination with theGhost_k8 suggestion

shaunak

ASKER

Thanks hielo
Your suggestion worked for all possibilities.
Thanks to theGhost_k8 for your comments.

shaunak

ASKER

Thanks hielo
Your suggestion worked for all possibilities.
Thanks to theGhost_k8 for your comments.

shaunak

ASKER

Thanks for the pin point solution

shaunak

ASKER

Some mess up in comments I think.

theGhost_k8 suggestion of
SELECT *
FROM `new_view`
WHERE FIND_IN_SET( '4', prod_ids ) >0
LIMIT 0 , 30
worked for me