用于 WHERE IN 的 SQL 分解字符串

Question

我在SQL Server中有一个表，其中有一列存储一系列字符。我希望能够将一系列字符发送到存储过程并将其用于WHERE IN。下面的示例

ID | series
---+-------
1 | U
2 | B
3 | R
4 | UB
5 | BR

我希望退货如下：

Parameter | Return IDs
----------+------------
U        | 1
R        | 3
U, R     | 1, 3

我可以以任何方式格式化参数，UR 或 U、R 或其他任何方式。我可以在应用程序中分解它并调用该过程 N 次，但我宁愿不这样做，以便我可以在查询中使用 orderby。

Answer 1

如果您不能使用表值参数，这里有一种方法。

declare @table table (ID int, series varchar(16))
insert into @table
values
(1,'U'),
(2,'B'),
(3,'R'),
(4,'UB'),
(5,'BR')

declare @input varchar(4000) = 'U,R'
select
t.*
from @table t
cross apply dbo.DelimitedSplit8K(@input,',') x 
where
x.Item = t.series

或无交叉申请

;with cte as
(select * 
from dbo.DelimitedSplit8K(@input,',') x )
select
t.*
from @table t
inner join cte on Item = t.series 

以下是已被证明是拆分字符串的快速方法的函数：

CREATE FUNCTION [dbo].[DelimitedSplit8K] (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
/* "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
enough to cover VARCHAR(8000)*/
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),                          --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item       = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
GO

Answer 2

实现此目的的方法是传入一个分隔字符串，然后创建一个函数来接收您的参数，拆分出值并返回一个表值。然后，您需要做的就是创建一个类似于 的存储过程。

CREATE PROCEDURE MyProc
(
@MyParameter NVARCHAR(3000)
)
AS
SELECT 
ID,
Series
FROM
MyTable
WHERE
Series IN (SELECT ID FROM dbo.MyFunctionToSplitDelimitedString(@MyParameter,',')