Sarath Jasrin Sarath Jasrin - 7 months ago 20
SQL Question

Search comma separated string using LIKE in mysql

I have a table with 3 columns something like below,

expert table

id - 1589
name - Jhonny
expert_in - 1,12,8 (Values similar like this)


The experts_in contains another table's foreign key


experts_in table


id - 1
expert_in - painting


I want search experts who are expert in some jobs while searching for experts

SELECT * FROM `experts` WHERE expert_in LIKE 1%


The above query brings all experts with 11,12,13...etc. I want only exact word. I know LIKE will bring all. Is there any way to achieve this without altering table.
Thanks in advance.

Answer

You should use REGEXP.
Try this query:

SELECT * FROM experts 
    WHERE expert_in REGEXP '[[:<:]]1[[:>:]]';

Output: See Live Demo on SQLFiddle

Note: You can adjust searching string based on your requirement above REGEXP is searching exact word.