如何检查存储在varchar列中的逗号分隔列表中是否包含数字？

I have a table with a varchar column categoryIds. It contains some IDs separated by commas, for example:

我有一个varchar列categoryIds的表。它包含一些用逗号分隔的ID，例如：

id       categoryIds
-------------------- 
1        3,7,12,33,43

I want to do a select statement and check if an int exists in that column. Something like this:

我想做一个select语句并检查该列中是否存在int。像这样的东西：

select * 
from myTable 
where 3 in (categoryIds)

I know this is possible in MySQL by doing this, but can it be done in SQL Server as well?

我知道这可以通过这样做在MySQL中实现，但是它也可以在SQL Server中完成吗？

I have tried casting the int to a char, which runs the following statement:

我已经尝试将int转换为char，它运行以下语句：

select * 
from myTable 
where '3' in (categoryIds)

But it doesn't look like there's any "out of the box" support for comma separated lists as it returns nothing.

但它似乎没有任何“开箱即用”支持逗号分隔列表，因为它什么都不返回。

6 个解决方案

#1

You should really redesign this table to split out those values from being comma separated to being in individual rows. However, if this is not possible, you are stuck with doing string comparison instead:

您应该重新设计此表以将这些值从逗号分隔为单独的行。但是，如果无法做到这一点，那么您将继续进行字符串比较：

DECLARE @id INT = 3
DECLARE @stringId VARCHAR(50) = CAST(@id AS VARCHAR(50))

SELECT * 
FROM MyTable 
WHERE categoryIds = @stringId -- When there is only 1 id in the table
OR categoryIds LIKE @stringId + ',%' -- When the id is the first one
OR categoryIds LIKE '%,' + @stringId + ',%' -- When the id is in the middle
OR categoryIds LIKE '%,' + @stringId -- When the id is at the end

#2

SELECT * 
FROM myTable 
WHERE (',' + RTRIM(categoryIds) + ',') LIKE '%,' + @stringId + ',%'

Here @stringId is your text to be searched. In this way you can avoid unnecessary multiple where conditions

这里@stringId是你要搜索的文本。通过这种方式，您可以避免不必要的多重条件

Kind Regards, Raghu.M.

亲切的问候，Raghu.M。

#3

Not sure if this would be faster or slower than DavidG's suggestion, but in order to get the same matches with only one check, you can do:

不确定这是否比DavidG的建议更快或更慢，但为了只用一张支票获得相同的匹配，你可以这样做：

DECLARE @categoryId INT
SET @categoryId = 3

SELECT *
FROM myTable
WHERE CHARINDEX(',' + CAST(@categoryId AS VARCHAR(MAX)) + ',', ',' + categoryIds + ',') > 0

#4

You could use dynamic SQL like this:

您可以像这样使用动态SQL：

DECLARE     @categoryIds    nvarchar(50) = '1, 2, 3, 4, 5'

EXEC        ('SELECT      *
              FROM        myTable
              WHERE       categoryId IN (' + @categoryIds + ')')

#5

SELECT * FROM user_master WHERE (user_tags regexp '[[:<:]]10[[:>:]]' or user_tags regexp '[[:<:]]11[[:>:]]')

SELECT * FROM user_master WHERE（user_tags regexp'[[：<：]] 10 [[：>：]]'或user_tags regexp'[[：<：]] 11 [[：>：]]'）

#6

-4

use FIND_IN_SET() mysql function

使用FIND_IN_SET（）mysql函数

Syntax

句法

SELECT * FROM <table name> as a WHERE FIND_IN_SET(value to search in string,comma separated string);

Example

例

SELECT * FROM <table name> as a WHERE FIND_IN_SET(5,"1,2,3,4,5,6");

#1