将充满逗号分隔值的 varchar 传递给 SQLServerIN 函数

副本
动态 SQL 逗号分隔值查询
使用 Like 和 In 的参数化查询

我有一个 SQLServer 存储过程，其中我想传递一个 varchar充满逗号分隔的值到一个 IN函数。例如:

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';


SELECT *
FROM sometable
WHERE tableid IN (@Ids);

这当然行不通，我得到了一个错误:

当将 varchar 值‘1,2,3,5,4,6,7,98,234’转换为数据类型 int 时，转换失败。

如何在不构建动态 SQL 的情况下完成这一任务(或者类似的任务) ？

216132 次浏览
小开

如果不使用动态 SQL，则必须获取输入变量并使用拆分函数将数据放入临时表中，然后连接到临时表。

小开

可以创建返回表的函数。

所以你的陈述应该是

select * from someable
join Splitfunction(@ids) as splits on sometable.id = splits.id

下面是一个类似的函数。

CREATE FUNCTION [dbo].[FUNC_SplitOrderIDs]
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int


SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)


IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)


END
END
RETURN
END
小开

我以前编写过一个存储过程来演示如何做到这一点。 你基本上必须处理字符串。 我试着把代码放在这里，但格式都乱了。

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[uspSplitTextList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[uspSplitTextList]
GO


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO




/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
-- uspSplitTextList
--
-- Description:
--    splits a separated list of text items and returns the text items
--
-- Arguments:
--    @list_text        - list of text items
--    @Delimiter        - delimiter
--
-- Notes:
-- 02/22/2006 - WSR : use DATALENGTH instead of LEN throughout because LEN doesn't count trailing blanks
--
-- History:
-- 02/22/2006 - WSR : revised algorithm to account for items crossing 8000 character boundary
-- 09/18/2006 - WSR : added to this project
--
CREATE PROCEDURE uspSplitTextList
@list_text           text,
@Delimiter           varchar(3)
AS


SET NOCOUNT ON


DECLARE @InputLen       integer         -- input text length
DECLARE @TextPos        integer         -- current position within input text
DECLARE @Chunk          varchar(8000)   -- chunk within input text
DECLARE @ChunkPos       integer         -- current position within chunk
DECLARE @DelimPos       integer         -- position of delimiter
DECLARE @ChunkLen       integer         -- chunk length
DECLARE @DelimLen       integer         -- delimiter length
DECLARE @ItemBegPos     integer         -- item starting position in text
DECLARE @ItemOrder      integer         -- item order in list
DECLARE @DelimChar      varchar(1)      -- first character of delimiter (simple delimiter)


-- create table to hold list items
-- actually their positions because we may want to scrub this list eliminating bad entries before substring is applied
CREATE TABLE #list_items ( item_order integer, item_begpos integer, item_endpos integer )


-- process list
IF @list_text IS NOT NULL
BEGIN


-- initialize
SET @InputLen = DATALENGTH(@list_text)
SET @TextPos = 1
SET @DelimChar = SUBSTRING(@Delimiter, 1, 1)
SET @DelimLen = DATALENGTH(@Delimiter)
SET @ItemBegPos = 1
SET @ItemOrder = 1
SET @ChunkLen = 1


-- cycle through input processing chunks
WHILE @TextPos <= @InputLen AND @ChunkLen <> 0
BEGIN


-- get current chunk
SET @Chunk = SUBSTRING(@list_text, @TextPos, 8000)


-- setup initial variable values
SET @ChunkPos = 1
SET @ChunkLen = DATALENGTH(@Chunk)
SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)


-- loop over the chunk, until the last delimiter
WHILE @ChunkPos <= @ChunkLen AND @DelimPos <> 0
BEGIN


-- see if this is a full delimiter
IF SUBSTRING(@list_text, (@TextPos + @DelimPos - 1), @DelimLen) = @Delimiter
BEGIN


-- insert position
INSERT INTO #list_items (item_order, item_begpos, item_endpos)
VALUES (@ItemOrder, @ItemBegPos, (@TextPos + @DelimPos - 1) - 1)


-- adjust positions
SET @ItemOrder = @ItemOrder + 1
SET @ItemBegPos = (@TextPos + @DelimPos - 1) + @DelimLen
SET @ChunkPos = @DelimPos + @DelimLen


END
ELSE
BEGIN


-- adjust positions
SET @ChunkPos = @DelimPos + 1


END


-- find next delimiter
SET @DelimPos = CHARINDEX(@DelimChar, @Chunk, @ChunkPos)


END


-- adjust positions
SET @TextPos = @TextPos + @ChunkLen


END


-- handle last item
IF @ItemBegPos <= @InputLen
BEGIN


-- insert position
INSERT INTO #list_items (item_order, item_begpos, item_endpos)
VALUES (@ItemOrder, @ItemBegPos, @InputLen)


END


-- delete the bad items
DELETE FROM #list_items
WHERE item_endpos < item_begpos


-- return list items
SELECT SUBSTRING(@list_text, item_begpos, (item_endpos - item_begpos + 1)) AS item_text, item_order, item_begpos, item_endpos
FROM #list_items
ORDER BY item_order


END


DROP TABLE #list_items


RETURN


/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
小开

这个非常好用！下面的答案太复杂了。别把这看成是动态的。设置存储过程如下:

(@id as varchar(50))
as


Declare @query as nvarchar(max)
set @query ='
select * from table
where id in('+@id+')'
EXECUTE sp_executesql @query
小开
最佳答案

不要使用循环来拆分字符串的函数！ ，下面的函数将非常快速地拆分一个字符串，没有循环！

在使用我的函数之前，您需要设置一个“ helper”表，每个数据库只需要这样做一次:

CREATE TABLE Numbers
(Number int  NOT NULL,
CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
SET @x=@x+1
INSERT INTO Numbers VALUES (@x)
END

使用这个函数来分割你的字符串，它不循环，而且非常快:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn              char(1)              --REQUIRED, the character to split the @List string on
,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
ListValue varchar(500)
)
AS
BEGIN


/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.




Returns a table, one row per item in the list, with a column name "ListValue"


EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')


returns:
ListValue
-----------
1
12
123
1234
54321
6
A
*
|||
B


(10 row(s) affected)


**/






----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
(ListValue)
SELECT
ListValue
FROM (SELECT
LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
FROM (
SELECT @SplitOn + @List + @SplitOn AS List2
) AS dt
INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
WHERE SUBSTRING(List2, number, 1) = @SplitOn
) dt2
WHERE ListValue IS NOT NULL AND ListValue!=''






RETURN


END --Function FN_ListToTable

您可以将此函数用作联接中的表:

SELECT
Col1, COl2, Col3...
FROM  YourTable
INNER JOIN FN_ListToTable(',',@YourString) s ON  YourTable.ID = s.ListValue

下面是你的例子:

Select * from sometable where tableid in(SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids) s)
小开

这是一个非常常见的问题。罐装答案，几个不错的技巧:

Http://www.sommarskog.se/arrays-in-sql-2005.html

小开

当然，如果你像我一样懒，你可以这样做:

Declare @Ids varchar(50) Set @Ids = ',1,2,3,5,4,6,7,98,234,'


Select * from sometable
where Charindex(','+cast(tableid as varchar(8000))+',', @Ids) > 0
小开

谢谢，为了你的功能，我使用了 IT... ... ... ... ... ..。 这是我的例子

**UPDATE [RD].[PurchaseOrderHeader]
SET     [DispatchCycleNumber] ='10'
WHERE  OrderNumber in(select * FROM XA.fn_SplitOrderIDs(@InvoiceNumberList))**




CREATE FUNCTION [XA].[fn_SplitOrderIDs]
(
@OrderList varchar(500)
)
RETURNS
@ParsedList table
(
OrderID int
)
AS
BEGIN
DECLARE @OrderID varchar(10), @Pos int


SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)


IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
INSERT INTO @ParsedList (OrderID)
VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)


END
END
RETURN
END
小开 
-- select * from dbo.Split_ID('77,106')


ALTER FUNCTION dbo.Split_ID(@String varchar(8000))
returns @temptable TABLE (ID varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
declare @Delimiter char(1)
set @Delimiter =','


select @idx = 1
if len(@String)<1 or @String is null  return


while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String


if(len(@slice)>0)
insert into @temptable(ID) values(@slice)


set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
小开

没有表，没有函数，没有循环

基于将列表解析为表的想法，我们的 DBA 建议使用 XML。

Declare @Ids varchar(50)
Set @Ids = ‘1,2,3,5,4,6,7,98,234’


DECLARE @XML XML
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)


SELECT *
FROM
SomeTable
INNER JOIN @XML.nodes('i') x(i)
ON  SomeTable .Id = x.i.value('.', 'VARCHAR(MAX)')

这些看起来和@KM 的答案有相同的性能，但是，我认为，要简单得多。

小开

如果使用 SQLServer2008或更高版本，请使用表值参数; 例如:

CREATE PROCEDURE [dbo].[GetAccounts](@accountIds nvarchar)
AS
BEGIN
SELECT *
FROM accountsTable
WHERE accountId IN (select * from @accountIds)
END


CREATE TYPE intListTableType AS TABLE (n int NOT NULL)


DECLARE @tvp intListTableType


-- inserts each id to one row in the tvp table
INSERT @tvp(n) VALUES (16509),(16685),(46173),(42925),(46167),(5511)


EXEC GetAccounts @tvp
小开

最好最简单的方法。

DECLARE @AccumulateKeywordCopy NVARCHAR(2000),@IDDupCopy NVARCHAR(50);
SET @AccumulateKeywordCopy ='';
SET @IDDupCopy ='';
SET @IDDup = (SELECT CONVERT(VARCHAR(MAX), <columnName>) FROM <tableName> WHERE <clause>)


SET @AccumulateKeywordCopy = ','+@AccumulateKeyword+',';
SET @IDDupCopy = ','+@IDDup +',';
SET @IDDupCheck = CHARINDEX(@IDDupCopy,@AccumulateKeywordCopy)
小开

你可以这样做:

create or replace
PROCEDURE UDP_SETBOOKMARK
(
P_USERID IN VARCHAR2
, P_BOOKMARK IN VARCHAR2
) AS
BEGIN


UPDATE T_ER_Bewertung
SET LESEZEICHEN = P_BOOKMARK
WHERE STAMM_ID in( select regexp_substr(P_USERID,'[^,]+', 1, level) from dual
connect by regexp_substr(P_USERID, '[^,]+', 1, level) is not null )
and ER_ID = (select max(ER_ID) from T_ER_Bewertung_Kopie);


commit;


END UDP_SETBOOKMARK;

那就试试

Begin
UDP_SETBOOKMARK ('1,2,3,4,5', 'Test');
End;

您也可以在其他情况下对 regexp _ subr 使用这个 IN 子句，只要尝试一下就可以了。

小开

我可以建议这样使用 WITH:

DECLARE @Delim char(1) = ',';
SET @Ids = @Ids + @Delim;


WITH CTE(i, ls, id) AS (
SELECT 1, CHARINDEX(@Delim, @Ids, 1), SUBSTRING(@Ids, 1, CHARINDEX(@Delim, @Ids, 1) - 1)
UNION ALL
SELECT i + 1, CHARINDEX(@Delim, @Ids, ls + 1), SUBSTRING(@Ids, ls + 1, CHARINDEX(@Delim, @Ids, ls + 1) - CHARINDEX(@Delim, @Ids, ls) - 1)
FROM CTE
WHERE  CHARINDEX(@Delim, @Ids, ls + 1) > 1
)
SELECT t.*
FROM yourTable t
INNER JOIN
CTE c
ON t.id = c.id;
小开

虽然已经有一段时间了，但我过去曾使用 XML 作为过渡。

这不能归功于我，但恐怕我已经不知道这个想法从何而来了:

-- declare the variables needed
DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)


-- The string you want to split
SET @str='A,B,C,D,E,Bert,Ernie,1,2,3,4,5'


-- What you want to split on. Can be a single character or a string
SET @delimiter =','


-- Convert it to an XML document
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)


-- Select back from the XML
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)
小开

我有同样的想法与用户知识管理。但不需要额外的表号码。只是这个功能。

CREATE FUNCTION [dbo].[FN_ListToTable]
(
@SplitOn              char(1)              --REQUIRED, the character to split the @List string on
,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
ListValue varchar(500)
)
AS
BEGIN
DECLARE @number int = 0
DECLARE @childString varchar(502) = ''
DECLARE @lengthChildString int = 0
DECLARE @processString varchar(502) = @SplitOn + @List + @SplitOn


WHILE @number < LEN(@processString)
BEGIN
SET @number = @number + 1
SET @lengthChildString = CHARINDEX(@SplitOn, @processString, @number + 1) - @number - 1
IF @lengthChildString > 0
BEGIN
SET @childString = LTRIM(RTRIM(SUBSTRING(@processString, @number + 1, @lengthChildString)))


IF @childString IS NOT NULL AND @childString != ''
BEGIN
INSERT INTO @ParsedList(ListValue) VALUES (@childString)
SET @number = @number + @lengthChildString - 1
END
END
END


RETURN


END

测试是这样的:

SELECT ListValue FROM dbo.FN_ListToTable('/','a/////bb/c')

结果:

   ListValue
______________________
a
bb
c
小开

创建一个像下面这样的表函数，解析逗号分隔的 varchar 并返回一个可以与其他表内部连接的表。

CREATE FUNCTION [dbo].[fn_SplitList]
(
@inString     varchar(MAX)  = '',
@inDelimiter  char(1)       = ',' -- Keep the delimiter to 100 chars or less.  Generally a delimiter will be 1-2 chars only.
)
RETURNS @tbl_Return  table
(
Unit  varchar(1000) COLLATE Latin1_General_BIN
)
AS
BEGIN
INSERT INTO @tbl_Return
SELECT DISTINCT
LTRIM(RTRIM(piece.value('./text()[1]', 'varchar(1000)'))) COLLATE DATABASE_DEFAULT AS Unit
FROM
(
--
--  Replace any delimiters in the string with the "X" tag.
--
SELECT
CAST(('<X>' + REPLACE(s0.prsString, s0.prsSplitDelimit, '</X><X>') + '</X>') AS xml).query('.') AS units
FROM
(
--
--  Convert the string and delimiter into XML.
--
SELECT
(SELECT @inString FOR XML PATH('')) AS prsString,
(SELECT @inDelimiter FOR XML PATH('')) AS prsSplitDelimit
) AS s0
) AS s1
CROSS APPLY units.nodes('X') x(piece)
RETURN
END

================================================= 现在在代码中使用上面创建的表函数，创建函数是数据库中的一次性活动，可以跨数据库使用，也可以在同一服务器上使用。

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';


SELECT
*
FROM sometable AS st
INNER JOIN fn_SplitList(@ids, ',') AS sl
ON sl.unit = st.tableid
小开

我发现的最简单的方法是使用 FIND _ IN _ SET

FIND_IN_SET(column_name, values)


values=(1,2,3)


SELECT name WHERE FIND_IN_SET(id, values)
小开

我认为一个非常简单的解决方案可以是:

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';


SELECT *
FROM sometable
WHERE ','+@Ids+',' LIKE '%,'+CONVERT(VARCHAR(50),tableid)+',%';
小开 
Error 493: The column 'i' that was returned from the nodes() method cannot be
used directly. It can only be used with one of the four XML data type
methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT
NULL checks.

通过使用下面的代码片段，在 SQLServer2014中修复了上述错误

Declare @Ids varchar(50)
Set @Ids = '1,2,3,5,4,6,7,98,234'


DECLARE @XML XML
SET @XML = CAST('<i>' + REPLACE(@Ids, ',', '</i><i>') + '</i>' AS XML)


SELECT SomeTable.*
FROM
SomeTable
cross apply @XML.nodes('i') x(i)
where SomeTable .Id = x.i.value('.', 'VARCHAR(MAX)')
小开

试试这个:

SELECT ProductId, Name, Tags
FROM Product
WHERE '1,2,3,' LIKE '%' + CAST(ProductId AS VARCHAR(20)) + ',%';

正如在 这个链接的最后一个例子中所说的

小开 
CREATE TABLE t
(
id   INT,
col1 VARCHAR(50)
)


INSERT INTO t
VALUES     (1,
'param1')


INSERT INTO t
VALUES     (2,
'param2')


INSERT INTO t
VALUES     (3,
'param3')


INSERT INTO t
VALUES     (4,
'param4')


INSERT INTO t
VALUES     (5,
'param5')


DECLARE @params VARCHAR(100)


SET @params = ',param1,param2,param3,'


SELECT *
FROM   t
WHERE  Charindex(',' + Cast(col1 AS VARCHAR(8000)) + ',', @params) > 0

工作小提琴在这里找到 小提琴

小开

这对于我的一个需求来说很方便，因为我不想使用 CTE，也不想使用内部连接。

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';
    

SELECT
cn1,cn2,cn3
FROM tableName
WHERE columnName in (select Value from fn_SplitString(@ids, ','))

用于拆分字符串的函数:

CREATE FUNCTION [dbo].[fn_SplitString] ( @stringToSplit VARCHAR(MAX), @seperator Char )
RETURNS
@returnList TABLE ([Value] [nvarchar] (500))
AS
BEGIN
  

DECLARE @name NVARCHAR(255)
DECLARE @pos INT
  

WHILE CHARINDEX(@seperator, @stringToSplit) > 0
BEGIN
SELECT @pos  = CHARINDEX(@seperator, @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
  

INSERT INTO @returnList
SELECT @name
  

SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
  

INSERT INTO @returnList
SELECT @stringToSplit
  

RETURN
END
小开

我也遇到了同样的问题，我不想在源数据库上留下任何痕迹——即没有存储过程或函数。我是这么想的:

declare @IDs table (Value int)


insert into @IDs values(1)
insert into @IDs values(2)
insert into @IDs values(3)
insert into @IDs values(5)
insert into @IDs values(4)
insert into @IDs values(6)
insert into @IDs values(7)
insert into @IDs values(98)
insert into @IDs values(234)




SELECT *
FROM sometable
WHERE tableid IN (select Value from @IDs)
小开

@ RBARryYoung(上图)给出的答案对我很有效。 但是如果在逗号分隔的字符串值之间有空格，那么它将省略带空格的 ID。所以我移除了空格。

请看下面的代码片段。

Declare @Ids varchar(50) Set @Ids = '1   ,   2,3'
set @Ids=','+Replace(@Ids,' ', '')+',';


Select * from [tblEmployee]
where Charindex(','+cast(ID as varchar(8000))+',', @Ids) > 0
小开

这里有很多答案，但是我认为 STRING_SPLIT是解决这类问题的一个非常简单的方法:

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';


SELECT *
FROM sometable
WHERE tableid IN;
(SELECT value FROM STRING_SPLIT(@Ids, ','))
小开 
WHERE someId IN (SELECT convert(int, value) FROM string_split(@stringOfCommaDelimitedIds, ','))

提交答案