How to create a T-SQL Function to parse a delimited list of numbers and return a table of those numbers

thank you albao

This post is related to and a supplement to a previous post “Pass Delimited List to SQL Server Stored Procedure”

Since parsing a delimited string and using the values of a delimited items is a common task in T-SQL, it is a good idea to create a T-SQL Function that will do the work of parsing the delimited string and then return the values in a Table.

Once the Function is created, you can call the Function from a Stored Procedure, Query, or whatever else can call a Function.

For our example, imagine you have a Table of Users. Where the table consists of the Columns: FirstName, LastName, EmailAddress, and ReceiveNewsletter. Our imaginary application has a feature that will send out monthly newsletters to users that would like to receive the monthly newsletters. To keep track of whether or not a user would like to receive the newsletter, we have a column in the Users table that accepts a bit value (True/False).

Now imagine the scenario where you want to create a Store Procedure that will update the ReceiveNewsletter column for multiple users using only one UPDATE statement. To do this we will create a sproc that receives a delimited list of UserId’s and a True/False value that specifies whether or not the specified UserId’s want to receive the newsletter or not.

However, due to T-SQL limitations, we cannot simply create an UPDATE statement in our sproc that looks like:

1 2 3 4

CREATE PROCEDURE MySproc @ids varchar(50) AS UPDATE MyTable SET Col1 = 1 WHERE  ID IN (@ids)

The result of calling the sproc above is an error message that reads something like:

Syntax error converting the varchar value ’1,2,3′ to a column
of data type int.

(Assuming that the caller of the sproc sent “1,2,3″ as the @ids value.

To work around this limitation, we will create a Function that does the work of parsing the delimited list of ids and then return the values in a Table. The caller of the Function can then simply SELECT the values from the returned table.

For example:

1 2 3 4 5 6 7 8 9 10

-- The caller of the sproc will send a delimited list of MyTable ID's such as "1,23,456,789"   -- Stored Procedure CREATE PROCEDURE MySproc @DelimitedList nvarchar(MAX) AS UPDATE MyTable SET Column1 = 1 WHERE ID IN (SELECT * FROM MyFunction) RETURN

Using Microsoft SQL Server Management Studio, this post will show you how to create the Function that will return a Table that consists of one column (named “Numbers”). The values/records of the “Numbers” column will consist of integer values that were parsed from a delimited string.

1. Open Microsoft SQL Server Management Studio
2. Open the database that you would like to add the Function to
3. Add a table to the database named Users. You can add the table manually or just run this script on your database:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   CREATE TABLE [dbo].[Users](     [UserId] [int] IDENTITY(1,1) NOT NULL,     [FirstName] [nvarchar](50) NULL,     [LastName] [nvarchar](50) NULL,     [EmailAddress] [nvarchar](50) NULL,     [ReceiveNewsletter] [bit] NULL ) ON [PRIMARY]   GO   INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Tom', 'Araya', 'tom@slayer.com', 'False') INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Frank', 'Zappa', 'frank@zappa.com','False') INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Johann', 'Bach', 'js@bach.com','False') INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Mikael', 'Akerfeldt', 'growler@opeth.com','False') INSERT INTO dbo.Users (FirstName, LastName, EmailAddress, ReceiveNewsletter) VALUES('Sonny', 'Stitt', 'sonny@stitt.com','False')

4. Navigate to the “Programmability” folder of your database
5. Right-click the “Functions” folder and select “New < Multi-Statement Table-Valued Function”
   
6. Add the following T-SQL code to your new Function:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

   -- The name of our Function will be "ParseNumbersFromDelimitedList" and will take two Parameters: 1. the delimited list and 2. the delimiter CREATE FUNCTION [dbo].[ParseNumbersFromDelimitedList](@DelimitedList nvarchar(max),@Delimiter varchar(1)) RETURNS   -- Create the schema of the Table that we will return to the caller @table TABLE (Number int) AS BEGIN   -- Remove white space from the list SET @DelimitedList = REPLACE(@DelimitedList, ' ','');   -- Var that will hold the value of the delimited item during the while-loop DECLARE @ItemInScope VARCHAR(MAX)   WHILE(LEN(@DelimitedList) > 0) BEGIN     -- If a delimiter exists in the string/list, then get the value to the left of the delimter.     -- Otherwise, just get the value of the string/list. This means that there is only one item in the string/list     IF(CHARINDEX(@Delimiter, @DelimitedList) > 0)         SET @ItemInScope = LEFT(@DelimitedList, CHARINDEX(@Delimiter, @DelimitedList))     ELSE         SET @ItemInScope = @DelimitedList       -- Remove the @NumberInScope value from the @NumericList     SET @DelimitedList = RIGHT(@DelimitedList, LEN(@DelimitedList) - LEN(@ItemInScope))       -- Remove the delimiter from the @NumberInScope     SET @ItemInScope = REPLACE(@ItemInScope,@Delimiter,'')       -- Print only the integer values     IF(ISNUMERIC(@ItemInScope) = 1)     BEGIN         -- Fill the table variable with the rows for your result set         INSERT INTO @table         (NUMBER)         VALUES         (@ItemInScope)     END END RETURN END

7. Execute/Save the Function
8. Now, we will create a Stored Procedure that will update the Users table. Specifically, we will create a sproc that will SET the “ReceiveNewsletter” column to ‘TRUE’ or ‘FALSE’ for records that have a matching UserId value that exists in a delimited string.
   In SSMS, add the following sproc:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   CREATE PROCEDURE SetReceiveNewsletter     @UserIdList NVARCHAR(MAX), -- The caller will pass a delimited string of UserId's     @Delimiter NVARCHAR(1) = ',', -- The caller can specify what the delimiter is. The default delimiter is a comma.     @ReceiveNewsletter bit = 'FALSE' -- The caller can specify 'TRUE' or 'FALSE' as the value that will update the records. Default is 'FALSE'. AS BEGIN         -- Update User records where the UserId is in the @UserIdList     UPDATE dbo.Users     SET ReceiveNewsletter = @ReceiveNewsletter     WHERE UserId IN                 -- Here is where we call the Function. The Function returns a Table of Numbers that existed in the @UserIdList         (SELECT * FROM dbo.ParseNumbersFromDelimitedList(@UserIdList,@Delimiter)) END GO

9. We now have a Function that does the dirty work of parsing a delimited string and returning any integers that were in the string (the UserId’s) in a Table that has a column named “Numbers”. All we need to do now is call the Stored Procedure …
10. Using SSMS, call the sproc:

    1 2 3 4 5

    EXEC    [dbo].[SetReceiveNewsletter]     @UserIdList = N'1,4,5', -- Here's a delimited string of UserId's that the caller passes to the sproc     @Delimiter = N',', -- The caller can specify the delimiter character     @ReceiveNewsletter = TRUE -- Set the ReceiveNewsletter column to TRUE for all UserId's that exist in the @UserIdList GO

    Here are the results:

    UserId	FirstName	LastName	EmailAddress	ReceiveNewsletter
    1	Tom	Araya	tom@slayer.com	True
    2	Frank	Zappa	frank@zappa.com	False
    3	Johann	Bach	js@bach.com	False
    4	Mikael	Akerfeldt	growler@opeth.com	True
    5	Sonny	Stitt	sonny@stitt.com	True

11. Conclusion
    Since T-SQL doesn’t parse a delimited string when using the IN clause in a T-SQL statement, we need to first parse the values out of the delimited string variable then add those values to a T-SQL Table. Then, we can use that Table to select the values within our statement.
    And since this is a common problem when SELECT’ing, UPDATE’ing, etc, we built a function that does the work of parsing the delimited string and adding those values to a Table. Callers of the Function simply have to pass the delimited string to the Function then work off of the returned Table.