How to compare multiple value in comma separated string using IN, FIND_IN_SET?

Question

I have multiple values in comma separated

(1,3,5) want to compare with (2,3,4,5,7,5) and this set refer to column value. So it should return 3 and 5

and this values are dynamic

I have used

SELECT * FROM table WHERE FIND_IN_SET('3', ('2,3,4,5,7,5')) AND FIND_IN_SET('5', ('2,3,4,5,7,5')) and so on

but it very tedius let me know any better solution for this.

Tip: _normalize your data_. Store your values in separate table. Good reason for this is: _you need to work with separate values, not entire string_ — Alma Do, Mar 18 '14 at 09:27
@AlmaDo yes i can understand this but i cant change now the database i have to work out with this only because for normalize db it required so many changes in site — Yadav Chetan, Mar 18 '14 at 09:28
Your architecture is _'tedius'_ as you've said. Thus, you'll need to apply such solution. Another way may be to use application ([`array_intersect()`](http://php.net/array_intersect) is for that in PHP) — Alma Do, Mar 18 '14 at 09:33
@AlmaDo but the other string to which i have to compare is dynamic and generated from database — Yadav Chetan, Mar 18 '14 at 09:35
So: yes, it is: select all and then `array_intersect` in loop for each row. Is it shorter? Yes. Is it faster? No. But you'll need to decide what do you want — Alma Do, Mar 18 '14 at 09:37
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/49924/discussion-between-yadav-chetan-and-alma-do) — Yadav Chetan, Mar 18 '14 at 09:39
See [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/a/3653574) — eggyal, Mar 18 '14 at 09:40
@YadavChetan: Huh? It's *really that bad* however many values it contains. — eggyal, Mar 18 '14 at 09:42
@eggyal i think so .. because if i use to store user id that subscribe some newsletter in comma separated then its not good to do like this but if there is only value lets take example days in comma separated then its ok.. its according to me please explore my knowledge if i am wrong — Yadav Chetan, Mar 18 '14 at 09:45
Read the answer to which I linked. You are wrong. If you have a list of values of some fixed set and which are not FKs into any other table and you do not need to lookup individual elements using an index, then you *might* consider using MySQL's `SET` datatype... but **never** use a freeform string column for this purpose and **almost always** prefer normalisation. — eggyal, Mar 18 '14 at 09:46
It is possible in MySQL (see http://stackoverflow.com/questions/22220256/how-to-use-find-in-set-for-two-strings-to-match-in-target-or-anything/22222111#22222111 ) but not really advisable. — Kickstart, Mar 18 '14 at 09:56

score 2 · Answer 1 · edited May 23 '17 at 10:31

Short answer

You should avoid this. While it actually can be done, your current architecture is violating at least first NF. And that's bad case. Storing delimiter-separated list is applicable only if you need to work with entire string, but not separate value itself. Therefore, most proper solution would be: create additional table and put your values there.

Long answer

This can be treated as some sort of puzzle - but I strongly do not recommend to use it on real application. So, let's suppose we have table t:

+------+------------------+
| id   | col              |
+------+------------------+
|    1 | 1,35,61,12,8     |
|    4 | 82,12,99,100,1,3 |
|    6 | 35,99,1          |
+------+------------------+

And we want to 'intersect' our strings with string '1,3,35'. I assume that your string is derived from application - therefore, you're able to do some preparations with it.

Final SQL will look like:

SELECT
  resulted.id,
  GROUP_CONCAT(resulted.sub) AS result
FROM
  (SELECT
    r.id, 
    TRIM(BOTH ',' FROM SUBSTR(
      r.col, 
      @cur,
      LOCATE(',', r.col, @cur+1)-@cur
    )) AS sub,
    @cur:=IF(
      CHAR_LENGTH(r.col)=LOCATE(',', r.col, @cur+1),
      1,
      LOCATE(',', r.col, @cur+1)
    ) AS cur
  FROM
    (SELECT
    id,
    CONCAT(TRIM(BOTH ',' FROM t.col), ',') AS col,
    CHAR_LENGTH(
      REPLACE(
      REPLACE(
      REPLACE(
      REPLACE(
      REPLACE(
      REPLACE(
      REPLACE(
      REPLACE(
      REPLACE(
      REPLACE(col
      , '9', '')
      , '8', '')
      , '7', '')
      , '6', '')
      , '5', '')
      , '4', '')
      , '3', '')
      , '2', '')
      , '1', '')
      , '0', '')
    ) + 1 AS repeats
    FROM t) AS r
    LEFT JOIN
    (SELECT
      (two_1.id + two_2.id + two_4.id + 
      two_8.id + two_16.id) AS id
     FROM
      (SELECT 0 AS id UNION ALL SELECT 1 AS id) AS two_1
      CROSS JOIN (SELECT 0 id UNION ALL SELECT 2 id) AS two_2
      CROSS JOIN (SELECT 0 id UNION ALL SELECT 4 id) AS two_4
      CROSS JOIN (SELECT 0 id UNION ALL SELECT 8 id) AS two_8
      CROSS JOIN (SELECT 0 id UNION ALL SELECT 16 id) AS two_16
     ) AS init
    ON init.id<r.repeats
    CROSS JOIN
      (SELECT @cur:=1) AS vars
   ) AS resulted
  INNER JOIN
  (SELECT '1' AS sub UNION ALL
   SELECT '3' UNION ALL
   SELECT '35'
  ) AS input
    ON resulted.sub=input.sub
GROUP BY
  resulted.id

(the demo is available here).

How it works

There are some tricks, that were used for this SQL. First, iteration variable. MySQL supports user-defined variables and they can be used for some sort of iterations in queries. And we're using it to pass valid offset and length into our string - to get piece of it via SUBSTR().

Next trick: we need to produce certain amount of rows - otherwise iteration won't work. That can be done the following way: count delimiters in each row and repeat it with that count+1. MySQL has no sequences, but there is third trick: to create desired count via huge CROSS JOIN (with summation of powers of 2 to get consecutive numbers). And that's for what internal LEFT JOIN is. In fact, I've faced this issue in one of my questions.

And, finally, we're doing INNER JOIN on entire result to get our intersected values. Note: this is the part, for which you'll need to make some preparations on your string. But it's easy to split string in application, getting needed UNION ALL part of query above.

What is out of the issue

Invalid strings. No checks will be done for things like '1,,,,4,5'. Really - it's not an intention of this method
Invalid non-numeric values. Since we're replacing 0..9 (that huge REPLACE part) - we can't do that dynamically - MySQL can't "replace any char, except.." This is a bottleneck, yes - but, again - not intention of the method

thanks for your effort ... finally i decided to do with find in set in loop.. in future i will avoid to do like this will keep in mind normalization — Yadav Chetan, Mar 18 '14 at 11:40
That was tricky. But - it's a way to resolve Jaywalker. Of course, normalization will save the world (and, I think, one look to SQL, posted in the answer, should convince anyone to be hurry and normalize their DB) — Alma Do, Mar 18 '14 at 11:45

score 1 · Answer 2 · answered Mar 18 '14 at 13:08

While I wouldn't recommend doing this in live code, it can be done without the need for variables:-

SELECT id, some_col, GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX('1,3,5', ',', AnInt), ',', -1) ORDER BY 1) AS anItem
FROM some_table
CROSS JOIN
(
    SELECT 1 + Units.i + Tens.i * 10 as AnInt
    FROM
    (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Units,
    (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) Tens
) Sub1
WHERE FIND_IN_SET(SUBSTRING_INDEX(SUBSTRING_INDEX('1,3,5', ',', AnInt), ',', -1), some_col)  
GROUP BY id, some_col

What this is doing is selecting 0 to 9 unioned, and joining this against itself. This gets 100 combinations, and by a bit of multiplication it gets the numbers 0 to 100. It then cross joins this against the table ou want to check, and uses this number as a parameter to SUBSTRING_INDEX to split it up on the commas. As such it can cope with ~100 numbers in the comma separated string you want to check. Down side is that it will duplicate some of these numbers, hence duplicates need to be removed.

The resulting numbers can then be used with FIND_IN_SET() to check the rows that contain these numbers in their comma separated field.

I have then used GROUP_CONCAT with DISTINCT to display the matching numbers for that row.

SQL Fiddle for it here:-

http://www.sqlfiddle.com/#!2/edf97/3

May be interesting, but [failing](http://www.sqlfiddle.com/#!2/edf97/4) for some cases — Alma Do, Mar 18 '14 at 16:06
@AlmaDo - Seems to work fine. You had added 100 as a number to check for only in one place (when doing the group_concat of values found, 100 needed to be added there as well) — Kickstart, Mar 18 '14 at 17:26
Indeed (high line was out of screen). It's applicable for this case, while I still prefer to dispatch whole values set so it will be able to do anything with it further — Alma Do, Mar 18 '14 at 18:07
@AlmaDo - would be easy to change it to do that. Just change it to doing a SELECT DISTINCT. I just used the GROUP_CONCAT to show the elements it would retrieve in one row rather than spread over several rows. — Kickstart, Mar 18 '14 at 21:36

How to compare multiple value in comma separated string using IN, FIND_IN_SET?

2 Answers2