CODEWITHSUNDEEP
mysql
Rahul Borole
Rahul Borole

Reputation: 53

MySQL Query : Match comma separated value with column containing comma separted string

I have a user table containing a column(say interests) with comma separated interest ids as a value. e.g.

user  interests
A     12,13,15
B     10,11,12,15
C     9,13
D     10,12

Now, I have a string with comma separated values as "13,15".

I want to fetch the users who has the interest 13,15 from above table means it should return the user A, B & C as user A contains both interest(13,15), user B matched the interest with 15 & user matched the interest with 13.

what will be the SQL as I have a lots of users in my table.

Upvotes: 1

Views: 5581

Answers (2)

1000111
1000111

Reputation: 13519

If you want to get the result based on loose matching then you can follow this query:

Loose matching means interests like 135,151 would also appear while searching for '13,15'.

SET @inputInterest := "13,15";

SELECT 
*
FROM userinterests
WHERE interests REGEXP REPLACE(@inputInterest,',','|');

For the given data you will get an output like below:

| ID | user |   interests |
|----|------|-------------|
|  1 |    A |    12,13,15 |
|  2 |    B | 10,11,12,15 |
|  3 |    C |        9,13 |

SQL FIDDLE DEMO

EDIT:

If you want result based on having at least one of the interests exactly then you can use regex as @Andrew mentioned in this answer:

Here's I've modified my query based on his insight:

SET @inputInterest := "13,15";

SELECT 
*
FROM userinterests
WHERE interests REGEXP CONCAT('(^|,)(', REPLACE(@inputInterest, ',', '|'), ')(,|$)')

SEE DEMO OF IT

Note:

You need to replace the @inputInterest variable by your input string.

Suggestion:

Is storing a delimited list in a database column really that bad?

Upvotes: 2

Andrew
Andrew

Reputation: 1866

It can be done with regexp as @1000111 said, but with more complicated regexp. Look at this, for example:

(^|,)(13|15)(,|$)

This will not match 13 from 135, or 1 from 13 and so on. For example, for number 13 this will match next strings:

1,13,2
13,1,2
1,13
13,2
13

But will not match these

1,135,2
131,2
1,113

And this is query:

SET @search = '13,15';

SELECT *
FROM test
WHERE interests REGEXP CONCAT('(^|,)(', REPLACE(@search, ',', '|'), ')(,|$)')

Upvotes: 9

Related Questions