CODEWITHSUNDEEP
mysql
max
max

Reputation: 331

How to match comma separated string against comma separated MySQL field

I’m trying to match comma separated string against comma separated MySQL field. This should match one more comma separated text.

Example

String = apple, pineapple, grapes

MySQL field = mango, oranges, apple

I tried MySQL IN but it only works if string exactly match the MySQL filed or if there is only one item in the string

SELECT * FROM table1 WHERE field_1 IN (‘string’)

Also tried FIND_IN_SET without any luck. Can anyone tell me how to achieve this? Appreciate your help.

Upvotes: 2

Views: 588

Answers (3)

Chintan7027
Chintan7027

Reputation: 7615

In order to achieve above result to match records, execute following script

Create temporary table with input string as rows by , separated

drop temporary table if exists temp;
create temporary table temp( val varchar(255) COLLATE utf8_unicode_ci )
DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
set @sql = concat("insert into temp (val) values ('", replace(( select 
group_concat(distinct ('mango,oranges,apple')) as data ), ",", "'),('"),"');");
prepare stmt1 from @sql;
execute stmt1;

Join temp table with main table

select *
from
(
     select distinct(val) as a from temp
)as splited
inner join 
(
    select 'apple, pineapple, grapes' as field
 )as table1
 where table1.field like concat('%',splited.a,'%');

Upvotes: 2

Bergin
Bergin

Reputation: 187

I feel like your question is wrong. you have mentioned as 

MySQL field = mango, oranges, apple

and your query is 

SELECT * FROM table1 WHERE field_1 IN (‘string’)

Make sure whether it is field name or field values

Upvotes: 0

Mason
Mason

Reputation: 1136

I think this answer might help you out. Just convert the cell value and the query value to an array, and then check if they have a matching value.

Upvotes: 0

Related Questions