compare two comma separated values in mysql

Question

I have a variable which contains comma separated value '1,2,3'

and my table is as below

id    favourite_id
1     2,5,6
2     3,5,7
3     6,1,3
4     5,6,7

I want to check my variable against favourite_id column to find at least one value is common in favourite_id. so I want output of mysql query as below

id    favourite_id
1     2,5,6
2     3,5,7
3     6,1,3

I know that this is not normalized table structure but I am not able to change my database structure.I have googled a lot but could not found suitable solution.

your output is based on what exactly? why rows 3 and 4 not shown that had 6? — user1646111
– user1646111, Commented Sep 5, 2013 at 6:29
You'll find single value for ex: contains id 5, something like that? — Bora
– Bora, Commented Sep 5, 2013 at 6:29
@Akam Based on the first line of the question, I guess the "checking" value was "1,2,3", which would return rows with the id of 1, 2 and 3 as these rows also have a 1, 2 or 3 in the favourite_id col. — Tigger
– Tigger, Commented Sep 5, 2013 at 6:31
@Akam I have variable which have value '1,2,3' in it so any one of of it should be there in favourite_id — Hardeep Pandya
– Hardeep Pandya, Commented Sep 5, 2013 at 6:32

Hardeep Pandya · Accepted Answer · 2013-09-25 07:00:54Z

7

Found two solution with the use of REGEXP of mysql

(1)

`favourite_id` REGEXP '[[:<:]]1[[:>:]]|[[:<:]]2[[:>:]]|[[:<:]]3[[:>:]]' //faster then below

(2)

`favourite_id` REGEXP '(^|,)(1|2|3)(,|$)' //slower then above

answered Sep 25, 2013 at 7:00

Hardeep Pandya

9151 gold badge8 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Muhammad · Accepted Answer · 2013-09-05 06:29:31Z

4

use FIND_IN_SET(str,strlist)

More detail: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

SELECT FIND_IN_SET('b','a,b,c,d');

answered Sep 5, 2013 at 6:29

Muhammad

3,2606 gold badges47 silver badges72 bronze badges

3 Comments

Hardeep Pandya Over a year ago

FIND_IN_SET will compare whole string and not each value in first operand so in my case if I compare '1,2,3' then output will be 0 result

Muhammad Over a year ago

then get all the results from the table, explode fav_ids and query them one by one with loop, but that is not a good approach.

Hardeep Pandya Over a year ago

there is thousands of records in table so it is not suitable to select and loop over result and then compare

Collectives™ on Stack Overflow

compare two comma separated values in mysql

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related