MYSQL match csv keywords column

Question

I have records in user table in following manner

id    name     address    keywords
1     thompsan paris      10,20,30
2     samson   paris      10,20,30
3     Nilawa   paris      10,20,30
4     Nalama   paris      100,30,50
5     Nalama   paris      100,300,20

I need to get the users who have the keywords of 10 or 20. I have written this query:

SELECT * from User where keywords REGEXP '[[:<:]]10|20[[:>:]]'

It does not give me the expected output. It should filter for id 10 or 20 and give me the output of record 1,2,3,5. record 4 is not matching here.

Why is it not working? Is there a better way to do this?

score 2 · Accepted Answer · answered Feb 24 '16 at 07:11

2

Try this,

SELECT  *
FROM    user
WHERE   FIND_IN_SET('10', keywords) > 0 OR
        FIND_IN_SET('20', keywords) > 0

FIND_IN_SET is a builtin function with MySQL

answered Feb 24 '16 at 07:11

Surabhil Sergy

1,946
1
23
40

isnt there a way to add a set of strings to the first param without adding one string? for example, FIND_IN_SET('10,20', keywords) – dev1234 Feb 24 '16 at 08:10

score 1 · Answer 2 · answered Feb 24 '16 at 07:13

Redesign your database so that it's actually in 1NF and you won't have to deal with these headaches not to mention the horrible performance and bugs that it's bound to bring you down the line.

Since I know that you won't do that though, there's no need to use REGEXP at all, assuming that your string in keywords is actually consistent (and if it's not then you're screwed anyway). Just use LIKE:

SELECT  -- We'll list out the columns, since we NEVER use SELECT *
    id,
    name,
    address,
    keywords
FROM
    User
WHERE
    ',' + keywords + ',' LIKE '%,10,%' OR
    ',' + keywords + ',' LIKE '%,20,%'

I tried this and got no output. SELECT * FROM User WHERE ',' + keywords + ',' LIKE '%,80,%' — dev1234, Feb 24 '16 at 08:21

MYSQL match csv keywords column

2 Answers2