select ID,
Name
from MyTable
where Name in ('Test1', 'Test2', 'Test3', 'Test4')
Name
from MyTable
where Name in (@List)
(
@List VARCHAR(8000),
@Delimiter VARCHAR(1)
)
RETURNS @Table TABLE
(
ID INT IDENTITY(1,1),
Value VARCHAR(100)
)
AS
BEGIN
-- loop through the list
WHILE (CHARINDEX(@Delimiter, @List) > 0)
BEGIN
-- add the value to the table
INSERT INTO @Table
(Value)
SELECT Value = LTRIM(RTRIM(SUBSTRING(@List, 1, CHARINDEX(@Delimiter, @List)-1)))
Set @List = SUBSTRING(@List, CHARINDEX(@Delimiter, @List) + LEN(@Delimiter), LEN(@List))
END -- insert remaining value from the list
INSERT INTO @Table
(Value)
SELECT Value = LTRIM(RTRIM(@List))
-- return
RETURN
END
This function uses a simple while loop that just processes part of the delimited list at a time, removes the value, and continues processing the rest of the string until there is nothing left to process.
The final results end up being a simple table with records representing the delimited string in a tableized format.
For example:
declare @List varchar(100)
declare @Delimiter varchar(1)
set
@List = 'Test1, Test2, Test3, Test4'set @Delimiter = ','
select
*from dbo.fnSplit(@List, @Delimiter)
Let's modify our query from earlier to utilize our new split function and we'll see how it all ties together:
declare @List varchar(100)
declare @Delimiter varchar(1)
set @List = 'Test1, Test2, Test3, Test4'
set @Delimiter = ','
select ID,
Name
from MyTable
where Name in (select Value from dbo.fnSplit(@List, @Delimiter))
We now simply can pass in our delimited list and delimiter and utilize it in a simple SQL statement to get the results we needed.
0 comments: