MySQL SELECT IN from string

Question

Here is my table X:

id      vals
---------------------
1       4|6|8|

Now table Y:

id      name
--------------------
1        a
4        b
6        c
8        d

Now I want the following:

select * from Y where id IN (replace(select vals from X where id = '1'),'|',',')

But this does not seem to work. Any ideas why?

Answer 1

You can use FIND_IN_SET instead of the usual one IN, a normal keyword INcannot search between comma-separated values ​​within the same field.

for example

mysql> select FIND_IN_SET(4, replace('4|6|8|','|',','));

+-------------------------------------------+
| FIND_IN_SET(4, replace('4|6|8|','|',',')) |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

Answer 2

Replace returns a string - but this is a string value, not a string as part of your query.

What you can do, instead of using IN, use REGEXP to match in your source string, for example:

vals REGEXP '[[:<:]]4[[:>:]]'

, "4", ( , 3 | 44 | 100, "4", "44" ).

[[:<:]] [[:>:]] - " " " " .

, - ...

CONCAT('[[:<:]]', CAST(id AS CHAR), '[[:>:]]')