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
LVL 8
shaunakAsked:
Who is Participating?
 
theGhost_k8Database ConsultantCommented:
Try:
where FIND_IN_SET('4',fieldname)>0
0
 
hieloCommented:
try:
SELECT *  FROM `new_view` WHERE CONCAT(',', `prod_ids`,',') LIKE '%4%'
0
 
shaunakAuthor Commented:
For hielo suggestions it also returns prodid with 14


Trying combination with theGhost_k8 suggestion
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
shaunakAuthor Commented:
For hielo suggestions it also returns prodid with 14


Trying combination with theGhost_k8 suggestion
0
 
shaunakAuthor Commented:
For hielo suggestions it also returns prodid with 14


Trying combination with theGhost_k8 suggestion
0
 
shaunakAuthor Commented:
Thanks hielo
Your suggestion worked for all possibilities.
Thanks to theGhost_k8 for your comments.


0
 
shaunakAuthor Commented:
Thanks hielo
Your suggestion worked for all possibilities.
Thanks to theGhost_k8 for your comments.


0
 
shaunakAuthor Commented:
Thanks for the pin point solution
0
 
shaunakAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.