Sarath Jasrin Sarath Jasrin - 1 year ago 62
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 Source

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.