CODEWITHSUNDEEP
mysql
Sarath Kumar
Sarath Kumar

Reputation: 1146

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.

Upvotes: 1

Views: 1446

Answers (2)

Piyush Gupta
Piyush Gupta

Reputation: 2179

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.

Upvotes: 3

chorn
chorn

Reputation: 150

if you can alter the data (not the table/schema) you should append and prepend another comma, so you can search with where col like "%,123,%", this will always fit on an exact value. Otherwise you have to use regex with something like ,?123,?

Upvotes: 1

Related Questions