在statemnt的IN子句中使用逗号分隔的varchar变量数据 [英] use a varchar variable data seperated by Commas inside the IN clause of where statemnt

问题描述

大家好，

谁能说出在statemnt的IN子句中如何使用varchar变量数据吗?

我的变量包含用逗号分隔的数据，该数据将在IN子句中使用.

我正在为研发使用以下sql:

Hi All,

Can anybody tell how to use a varchar variable data inside the IN clause of where statemnt?

My variable contains data seperated by commas which is to be used in the IN clause.

I am using following sql for R&D:

create table #temp
(a varchar(1000))
insert into #temp
values('A')
insert into #temp
values('B')
select * from #temp

Declare @qry varchar(100)
set @qry = 'A' + ',' + 'B'
--print @qry
select * from #temp where a IN(@qry)
drop table #temp

我知道不能将需要在IN子句中使用的逗号连接起来，但是我仍然不知道如何实现这种情况.

任何帮助将不胜感激.

问候，
Gopal

I have come to know that I can''t concatenate the comma that needs to be used in the IN clause, but still I dont know how to implement this kind of scenario.

Any help would be much appreciated.

Regards,
Gopal

推荐答案

看看此的结尾文章 [ ^ ].

如果您想那样做，还请务必阅读此 [ ^ ](确实可以这样做，或

And if you want to go that way, also be sure to read this[^] (really, do it or this will happen![^])
You have been warned :)

很遗憾，没有真正的方法来做到这一点.

IN是一个函数，并且需要一系列参数.例如:

Unfortunately, there is no real way to do that.

IN is a function, and expects a range of parameters. For example:

select * from #temp where a IN (A,B)

使用两个参数调用IN函数.在您的代码中，使用一个参数调用它:

calls the IN function with two parameters. In your code, you call it with one parameter:

set @qry = ''A'' + '','' + ''B''
select * from #temp where a IN(@qry)

写作的平衡点:

The equilavent of writing:

select * from #temp where a IN(''A,B'')

尝试:在临时表中添加一个值:

Try it: add a value to your temporary table:

create table #temp
(a varchar(1000))
insert into #temp
values(''A'')
insert into #temp
values(''B'')
insert into #temp
values(''A,B'')
select * from #temp
 
Declare @qry varchar(100)
set @qry = ''A'' + '','' + ''B''
--print @qry
select * from #temp where a IN(@qry)
drop table #temp

然后您将返回一行.

网上有一些工作回合，但是它们都很讨厌，涉及使用已知的定界符构造一个字符串，然后在循环中使用INSTR函数.示例如下: http://www.techrepublic.com/article/simulate-variable -arguments-in-plsql/5726322 [ ^ ]

基本上，如果您可以找到另一种方法，我会的！

And you will get a row returned.

There are a few work-rounds on the net, but they are all pretty nasty, and involved constructing a string with a known delimiter and then using the INSTR function in a loop. An example is here: http://www.techrepublic.com/article/simulate-variable-arguments-in-plsql/5726322[^]

Basically, if you can find another, way to do this, I would!

这篇关于在statemnt的IN子句中使用逗号分隔的varchar变量数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！