search number from string in php

Question

I have problem to search number from string which is simple but I can not solve, I have GALLERY table

id | gallery_name | standard
____________________________
1  | aaa          | 1,2    
2  | bbb          | 2
3  | ccc          | 1,6, 12

I want to find gallery which has 2 standard. For that I run below query

select * from galley where standard IN('%2%');

Output: it return first two row which contain 2 standard and also return 3 row which not contain 2 but 12

Please help me how to solve the issue

Thank you

I think you should normalize your table, but failing that, have a look at `FIND_IN_SET(str,strlist)` https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set — KIKO Software, Jul 13 '18 at 09:17
Possible duplicate of [need to filter data in mysql with data array](https://stackoverflow.com/questions/51317342/need-to-filter-data-in-mysql-with-data-array) — Praveen S, Jul 13 '18 at 09:30

flyingfox · Accepted Answer · 2018-07-13T09:28:03.543

3

You can use either FIND_IN_SET() OR LIKE

select * from galley FIND_IN_SET('2',standard);

OR

select * from galley 
  where standard LIKE '%,2' OR standard LIKE '2,%' OR standard LIKE '%,2,%';

edited Jul 13 '18 at 09:28

answered Jul 13 '18 at 09:18

flyingfox

2

I don't think your LIKE example will work. It will still bring the last row which has '12'. – Anjana Silva Jul 13 '18 at 09:23
1

@AnjanaSilva thanks for your comment,just updated my answer – flyingfox Jul 13 '18 at 09:28
@AnjanaSilva LIKE Example works. It will not bring last row with 12 – Praveen S Jul 13 '18 at 09:38
@PraveenS because I have updated my answer,thank you – flyingfox Jul 13 '18 at 09:39

1 Answers1