Convert a Delimited String in T-SQL

January 6, 2015 SQL / Databases

There are times when it is useful to pass a delimited string to SQL server. For example, I often take selections from a CheckBoxList and send them to a T-SQL stored procedure as a comm-delimited string.

MakeTableFromDelimitedString UDF

The following T-SQL table-value function will parse any delimited string and place each value in the string into a table whose only column is named val.

CREATE FUNCTION [dbo].[MakeTableFromDelimitedString]
(
   @string varchar(8000),
   @delimiter char(1) 
) 
RETURNS @values TABLE (val varchar(100)) 
AS
   BEGIN
      -- declare our temporary table (must match RETURNS statement)
      DECLARE @valuesWork TABLE (val varchar(100))

      -- parse the string 
      DECLARE @value varchar(100) 
      DECLARE @delim int 
      DECLARE @idx int 
      SET @idx = 1 
      SET @delim = CHARINDEX(@delimiter, @string, @idx) 
      WHILE @delim > 0   
         BEGIN 
            INSERT INTO @valuesWork (val) VALUES (SUBSTRING(@string, @idx, @delim - @idx))
            SET @idx = @delim + 1 
            SET @delim = CHARINDEX(@delimiter, @string, @idx)
         END
      INSERT INTO @valuesWork (val) VALUES (SUBSTRING(@string, @idx, 3500)) 

      -- return the results 
      INSERT @values 
         SELECT * FROM @valuesWork 

      RETURN
   END

Now you can take the results of this function and join it into a query. For example, if Consider the following tables:

Using the UDF

CREATE TABLE [dbo].[Client]
(
 [ClientID] [int] IDENTITY(1,1) NOT NULL,
 [ClientName] [nvarchar](100) NOT NULL,
  CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED  ( [ClientID] ASC )
)

CREATE TABLE [dbo].[Invoice]
(
 [InvoiceID] [int] IDENTITY(1,1) NOT NULL,
 [ClientID] [int] NOT NULL,
 [InvoiceAmoint] [money] NULL,
  CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED  ( [ClientID] ASC )
)

Below is an example stored procedure that ties everything together.

CREATE PROCEDURE DelimitedStringTest
   @clientIDs varchar(MAX) 
AS 
   SELECT i.*, c.ClientName
      FROM Invoice AS i 
         INNER JOIN Client AS c ON c.ClientID = i.ClientID
         INNER JOIN dbo.MakeTableFromDelimitedString(@clientIDs, ',') AS id ON id.val = i.ClientID

Using this technique, imagine you have a multiple-choice selection on your user interface that allows someone to select one or more clients. Now all you have to do is string together the selected client IDs, pass them to your stored procedure and viola, your query is instantly filtered by the contents of that delimited string.

Share It