mySQL SELECT IN from string

Question

Hello,

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

Replace gives you a string back - but it's a string value, not a string as in part of your query.

What you can do is instead of using IN, use a REGEXP to match within your original string, for example:

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

would be true only if there is a "4" in the original string that isn't part of a larger number (thus if you have 3|44|100 it wouldn't match on "4" but would match on "44").

The [[:<:]] and [[:>:]] are "left side of word" and "right side of word" respectively.

To generate that string, you can do something like...

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

Answer 2

You may use FIND_IN_SET instead of just IN, normal IN keyword couldn't search between comma seperated values within one 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)