Showing posts with label find-in-set. Show all posts
Showing posts with label find-in-set. Show all posts

Saturday, June 16, 2018

MySQL find_in_set Sith Multiple Search String | MySQL Variable With Multiple Values | MySQL Use of REGEXP | Search IN With Multiple Values

Problem is like below:
We know that find_in_set only search by a single string

find_in_set('2', '2,4,6,8')

In the above example, '2' is the only string used for search.

So we are looking for if there is any way to use find_in_set kind of functionality and search by multiple strings, like

find_in_set('2,4,6', '2,3,4,6,8,10')
The only solution is to use REGEXP
where CONCAT(",", t2.id, ",") REGEXP ",(2|4|6|8|10),"
Full query would be like below:
SELECT
  t1.*,
  SUM(t2.score)                               AS score_total,
  GROUP_CONCAT(t2.subject ORDER BY t2.id ASC) AS subjects,
  GROUP_CONCAT(t2.id ORDER BY t2.id ASC)      AS t2_id
FROM table1 t1 JOIN table2_child t2 ON (t1.id = t2.table1_id)
WHERE CONCAT(",", t2.id, ",") REGEXP ",(2|4|6|8|10),"
GROUP BY t1.id