SQL 在 IN 子句中使用逗号分隔的参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27606091/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using a comma-separated parameter in an IN clause
提问by Mikhail Sokolov
I have 'param1, param2, parma3'coming from SSRS to a stored procedure as a varcharparameter: I need to use it in a query's INclause but then need to change its format like this first:
我'param1, param2, parma3'从 SSRS 到存储过程作为varchar参数：我需要在查询的IN子句中使用它，但需要先像这样更改其格式：
select *
from table1
where col1 in('param1', 'param2', 'param3')
What is the best way to reformat the parameter without creating functions and parameter tables?
在不创建函数和参数表的情况下重新格式化参数的最佳方法是什么？
回答by HaveNoDisplayName
Try this one, Just need to add commas at the beginning and at the end of @params string.
试试这个，只需要在@params 字符串的开头和结尾添加逗号。
Declare @params varchar(100) Set @params = ',param1,param2,param3,'
Select * from t
where CHARINDEX(','+cast(col1 as varchar(8000))+',', @params) > 0
回答by Susheel Kumar
you can use split function and use it as in following way here my split fnSplitString return splitdata
您可以使用 split 函数并按照以下方式使用它我的 split fnSplitString return splitdata
select * from tb1 where id in(select splitdata from dbo.fnSplitString((select col1 from tb12 where id=3),','))
create FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output(splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END
回答by amit godse
If you are using SQL 2016 and above string_splityou can use.
如果您使用的是 SQL 2016 及更高版本string_split，则可以使用。
-- @param is where you keep your comma separated values example:
declare @param = 'param1,param2,param3'
select * from table1 where col1 in (select TRIM(value) from string_split(@param,',')
More information about string_split check offical documemt
有关 string_split 检查官方文档的更多信息
Furthermore, TRIM()is used to trim values from white spaces.
此外，TRIM()用于从空白处修剪值。
回答by J0e3gan
"Best way" is arguable, but one classic approach that remains without "creating functions and table parameters" is to simply employ dynamic SQL in the stored procedure:
“最好的方法”是有争议的，但一种没有“创建函数和表参数”的经典方法是简单地在存储过程中使用动态 SQL：
-- FORNOW: local to act as the SP param and arg
declare @values varchar(100) = 'param1, param2, param3'
-- Add opening and closing single quotes, then quotes around each
-- comma-separated list item.
select @values = '''' + REPLACE(@values, ', ', ''', ''') + ''''
-- FORNOW: for clarity/debugging
print @values
--'param1', 'param2', 'param3'
-- Run the desired query as dynamic SQL.
DECLARE @sql as nvarchar(250);
SET @sql = 'select * from table1 where col1 in (' + @values + ')';
EXEC sp_executesql @sql;
This assumes a couple things, though:
不过，这假设了几件事：
- That commas in the list of values are followed by a space. Variations on this solution can address deviations in this respect of course, but it is important to be aware of this assumption.
- That the comma-separated values do not themselves have commas in them – unlikely but worth mentioning since whether values will satisfy this constraint sometimes goes unconsidered.
- 值列表中的逗号后跟一个空格。该解决方案的变化当然可以解决这方面的偏差，但重要的是要注意这个假设。
- 逗号分隔的值本身没有逗号——不太可能但值得一提，因为值是否满足此约束有时不被考虑。
回答by Amir Pelled
Load the Params into a string and execute as an sql :
将参数加载到字符串中并作为 sql 执行：
declare @param varchar(1000) = 'param1, param2, parma3'
declare @sql varchar(4000)
select @sql =
'select *
from table1
where col1 in(''' + replace(@param,',',''',''') + ''')'
-- print @sql -- to see what you're going to execute
exec sp_executesql @sql
