如何将列值拆分为"IN"条件? [英] How to split a column value to a 'IN' condition?

问题描述

假设我有一个表t1，它有一个列effecitive_game，该列将存储名称以及逗号(或其他一些预定的字符，如';')，以便我进行过滤.

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.

例如:该列的值为'game1，game2，game3'

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

我希望用JDBC编写SQL，如下所示:

I hope to write a SQL in JDBC like:

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

如何将列值'game1，game2，game3'拆分为('game1'，'game2'，'game3')这样的集合，以便可以使用IN构造where条件? >

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?

推荐答案

您无需将逗号分隔的值转换为任何值. 使用FIND_IN_SET() MySQL函数

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],';%')

这篇关于如何将列值拆分为"IN"条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！