SQL语句中的Replace在IN类中不起作用

huangapple go评论114阅读模式
英文:

SQL Statement with Replace not working in IN Class

问题

以下 SQL 语句未返回任何记录:

Declare @sSelectedStates as nvarchar(500) = REPLACE('Bihar|Gujarat|Jharkhand','|','','')
select * from MyTable where State IN (@sSelectedStates)

这个有效:

Declare @sSelectedStates as nvarchar(500) = REPLACE('Bihar','|','','')
select * from MyTable where State IN (@sSelectedStates)

这个也有效:

Declare @sSelectedStates as nvarchar(500) = REPLACE('Gujarat','|','','')
select * from MyTable where State IN (@sSelectedStates)

我做错了什么?使用以下语句,我更改了数据库兼容性级别:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130
英文:

The below sql statement is not resulting any records.

Declare @sSelectedStates as nvarchar(500) = REPLACE('Bihar|Gujarat|Jharkhand','|',''',''')
select * from MyTable where State IN (@sSelectedStates)

This works

Declare @sSelectedStates as nvarchar(500) = REPLACE('Bihar','|',''',''')
select * from MyTable where State IN (@sSelectedStates)

This also works

Declare @sSelectedStates as nvarchar(500) = REPLACE('Gujarat','|',''',''')
select * from MyTable where State IN (@sSelectedStates)

What I am doing wrong?

Using the below statement I changed the database compatibility level

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

答案1

得分: 1

不能这样做。IN 操作符期望一个由逗号分隔的参数列表,但你提供的是一个包含逗号分隔字符串的单个参数。

标量变量只能包含单个值 - 当 IN 操作符提供一个单个值时,它的行为就像 = 操作符 - 所以你的查询基本上与下面这些查询是一样的:

select * from MyTable where State = 'Bihar','Gujarat','Jharkhand'

select * from MyTable where State = Bihar

select * from MyTable where State = Gujarat

如果你使用 SQL Server 2016 或更高版本,你可以使用内置的 string_split 函数将分隔的字符串转换为表格。

如果你使用较低版本,你可以使用一个用户定义的函数来为你拆分字符串。

英文:

You can't do it like this. The IN operator expects a list of parameters separated by comma, but you supply it with a single parameter that happens to contain a comma delimited string.

A scalar variable can only contain a single value - and when the IN operator is presented with a single value, it acts like the = operator - so your queries are basically the same as these:

select * from MyTable where State = 'Bihar'',''Gujarat'',''Jharkhand'

select * from MyTable where State = Bihar

select * from MyTable where State = Gujarat

If you're working with SQL Server 2016 or higher, you can use the built in string_split to convert the delimited string into a table.
If you're working on a lower version, you can use a user-defined function to split the string for you.

答案2

得分: 1

这是一个相当常见的误解,in集合上工作,而不是字符串 - 它不以任何方式解析您提供的单个字符串。

您可以通过将字符串拆分为行来为in子句提供一组值:

Declare @sSelectedStates nvarchar(500) = 'Bihar|Gujarat|Jharkhand';

select * 
from MyTable 
where State in (select * from String_Split(@sSelectedStates, '|'));
英文:

This is quite a common misconception, in works on a set not a string - it does not parse the single string you are providing in any way.

You can provide a set of values to the in clause by splitting the string into rows:

Declare @sSelectedStates nvarchar(500) = 'Bihar|Gujarat|Jharkhand';

select * 
from MyTable 
where State in (select * from String_Split(@sSelectedStates, '|'));

huangapple
  • 本文由 发表于 2023年6月19日 18:17:18
  • 转载请务必保留本文链接:https://go.coder-hub.com/76505663.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定