Wrap list elements in quotes, to be used in WHERE IN

Question

Assuming I got a long list of comma-separated values:

one, two, three, .., hundred

and I need to use them in a WHERE IN like this:

SELECT * FROM tags WHERE IN (one, two, three, .., hundred)

then is there any way I can "tell" MySQL to wrap each of the values in quotes, or at least make it treat each of the values as strings? Without wrapping the values in quotes, the WHERE IN doesn't work.

Note: Surely there are plenty of ways to modify the list of values beforehand and then pass it to the query, but I'd like to know if there's a MySQL way to achieve the same effect.

Fair enough. One can use `FIND_IN_SET` as an alternative, as mentioned by Lalabhai Patel. — lesssugar, Aug 29 '17 at 08:51

score 1 · Answer 1 · answered Aug 29 '17 at 08:48

1

Use this query

SELECT * FROM tags WHERE FIND_IN_SET(field_name,'one, two, three, hundred')

answered Aug 29 '17 at 08:48

Lalabhai Patel

169
6

The answer is not in the context of `WHERE IN` but maybe the post needed an out-of-the-box one. – lesssugar Aug 29 '17 at 08:52

score 1 · Answer 2 · answered Aug 29 '17 at 08:56

It's not possible with a Simple SQL query, however, you could do something like this:

Create a user defined function that splits the String by comma (as explained in this SO answer) and wrap each token into double quotes
Use CONCAT with parenthesis and store the resultant String into a variable (using SET)
Use the value of the above variable in IN query (e.g. WHERE value IN @val)
Execute all the queries in a batch or a procedure

Wrap list elements in quotes, to be used in WHERE IN

2 Answers2