Set variable with multiple values and use IN
Ideally you shouldn't be splitting strings in T-SQL at all.
Barring that change, on older versions before SQL Server 2016, create a split function:
CREATE FUNCTION dbo.SplitStrings
(
@List nvarchar(max),
@Delimiter nvarchar(2)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN ( WITH x(x) AS
(
SELECT CONVERT(xml, N'<root><i>'
+ REPLACE(@List, @Delimiter, N'</i><i>')
+ N'</i></root>')
)
SELECT Item = LTRIM(RTRIM(i.i.value(N'.',N'nvarchar(max)')))
FROM x CROSS APPLY x.nodes(N'//root/i') AS i(i)
);
GO
Now you can say:
DECLARE @Values varchar(1000);
SET @Values = 'A, B, C';
SELECT blah
FROM dbo.foo
INNER JOIN dbo.SplitStrings(@Values, ',') AS s
ON s.Item = foo.myField;
On SQL Server 2016 or above (or Azure SQL Database), it is much simpler and more efficient, however you do have to manually apply LTRIM()
to take away any leading spaces:
DECLARE @Values varchar(1000) = 'A, B, C';
SELECT blah
FROM dbo.foo
INNER JOIN STRING_SPLIT(@Values, ',') AS s
ON LTRIM(s.value) = foo.myField;
You need a table variable:
declare @values table
(
Value varchar(1000)
)
insert into @values values ('A')
insert into @values values ('B')
insert into @values values ('C')
select blah
from foo
where myField in (select value from @values)