CODEWITHSUNDEEP
mysql
Alec Smart
Alec Smart

Reputation: 95880

mySQL SELECT IN from string

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?

Upvotes: 4

Views: 10166

Answers (2)

YOU
YOU

Reputation: 123791

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)

Upvotes: 6

Amber
Amber

Reputation: 526483

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), '[[:>:]]')

Upvotes: 0

Related Questions