Building a Split() Function in SQL Server

So you've got a delimited list of values and you want to somehow pass those to SQL as a single parameter and use them in your query?

 

One style that would accomplish this behavior is to have your SQL statement built dynamically in one of your layers of code.

 

Another option would be to pass in the delimited list to a stored procedure and parse the string yourself with some string operations and a cursor and build a dynamic SQL statement and then use EXEC to get the results.

 

But I find myself typically wanting to use a more generic approach that has some level of reusability, hence a user defined function (UDF) to handle the split and keep the rest of the SQL statements very simple.

 

For example, let's say you have this SELECT statement:

select     ID,
             Name
from       MyTable
where     Name in ('Test1', 'Test2', 'Test3', 'Test4')

 

Nothing too exciting here. It simply returns the 4 records that we've asked for.

 

But what if we didn't know what records the user was going to ask for and we needed to make this statement more generic and parameterized.

 

Something like this would be nice; however, as you probably know, it's won't yield the results you need:

 

declare @List varchar(100)

 

set @List = 'Test1, Test2, Test3, Test4'

 

select    ID,
            Name
from      MyTable
where    Name in (@List)

 

Hence we introduce our user defined split function:

 

CREATE FUNCTION [dbo].[fnSplit]
(
     @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)))

          -- remove the value from the list
          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.