英文:
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, '|'));
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论