MySQL: how can i convert a string '1,2,3,4' to a (1,2,3,4) so i'll be able to use it in a 'where X in ()' statement

Question

I ran a query that resulted in the string '1,2,3,4'.

How can I run a second query that treats that string as a list of numbers. So I'll be able to do:

select * from tbl where name not in (1,2,3,4)

I would like an answer in pure MySQL.

Answer 1

Well first of all, this usually means that your database structure is not good; you should normalize your database.

However, you can do what you want, with the FIND_IN_SET function:

SELECT * FROM tbl WHERE NOT FIND_IN_SET(name, '1,2,3,4')

Answer 2

Use FIND_IN_SET:

select * from tbl where FIND_IN_SET(name, '1,2,3,4') = 0

Like the other answer, I would also recommend normalizing your database if at all possible. This query could be slow as it will require a scan of the table. Even if there is an index on name this query won't be able to use it efficiently.