How to split a column value to a 'IN' cond

问题:

Suppose I have a table t1, it has a column effecitive_game, which will store the names along with comma(OR some other predetermined chars like ';') so that I can use to filter.

For example: the column value is 'game1,game2,game3'

I hope to write a SQL in JDBC like:

"SELECT * FROM t1, WHERE "+ HERE_IS_A_PARAM_ + "IN " +" effecitive_game'//

how can I split the column value 'game1,game2,game3' into a set like ('game1','game2','game3') so that I can use IN to construct the where condition?

回答1:

You do not need to convert comma separated values to anything. Use FIND_IN_SET() MySQL function

SELECT * FROM t1 WHERE FIND_IN_SET([SERACHINGFOR], [COMMASEPARATEDLIST])>0

If you have to use another separator (lets say ';'), then you have to trick the system.

SELECT * FROM t1 WHERE CONCAT(";",[SEPARATEDLIST],";") LIKE CONCAT('%;',[SEARCHFOR],';%')