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

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:

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:

-- 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:
    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:
    -- 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:

    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:
    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.

If you are interested in learning more about working with lists and arrays in SQL Server, do yourself a favor and check out what Erland Sommarskog has to say.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s