在where子句中替换SQL server

Question

假设我们有一张桌子。此表为每个学生代码都有一个列(varchar)。我想根据我设置的过滤器搜索许多学生。

例如，我得到了一个问题字段(Varchar)，用户可以在其中插入用逗号分隔的代码，比如000012,000014,000058。

问题字段返回的值与'000012,000014,000058'查询以Select * from Students where code in ( '000012,000014,000058')方式实现问题字段的值类似

我必须将值更改为：('000012','000014','000058')，所以使用替换函数REPLACE(CHAR(39)+'000012,000014,000058'+CHAR(39),',',CHAR(39)+','+CHAR(39))，我尝试使用这个函数

Select * 
from students 
where code in (REPLACE(CHAR(39)+'000012,000014,000058'+CHAR(39),',',CHAR(39)+','+CHAR(39)))

但还是不起作用。

有什么想法吗？

Answer 1

您的REPLACE(...返回一个字符串，您需要的是类似于表的东西。有很多方法可以将字符串(varchar)拆分到表中。这只是其中之一。

declare @ids varchar(100)='000012,000014,000058'
declare @x xml='<x>'+replace(@ids,',','</x><x>')+'</x>'
select * from students
where code in (
select t.x.value('.[1]','varchar(10)')
from @x.nodes('x') t(x)
)

Answer 2

正如jpw所说，在单元格中使用逗号分隔的值违反了第一范式，因此SQL的查询能力已经被削弱。在这种情况下，您可以做的是首先恢复到正常形式，然后继续正常操作：

如果您使用的是SQL Server2016，则可以使用带有cross apply的string_split将单元格拆分为行(here's a backwards compatible string_split for earlier versions)，例如：

select s.* from students s cross apply string_split(code, ',')
where value in ('000012','000014','000058')

..alternatively如果你没有2016，也不想添加string_split函数，你可以使用.nodes，就像亚历克斯的答案一样：

select s.* from students s cross apply
(select convert(xml, '<x>' + replace(code, ',', '</x><x>') + '</x>')) a(code_xml)
cross apply code_xml.nodes('*') x(n)
where n.value('.','varchar(20)') in ('000012','000014','000058')

-编辑

刚刚注意到您在注释中声明数据库单元格不包括逗号分隔值-在这种情况下，Alex的答案是合适的。