Splitting Delimited Strings

Posted on by Gogula G. Aryalingam

Splitting delimited strings is a common requirement when writing T-SQL. There are so many instances when a comma separated list of product codes are required to be sent to the database for various types of processing. Once sent to the database the delimited string will have to be split and supplied as a parameter to a query or something like that.

With the introduction of SQL Server 2008, one would have expected the usage of splitting strings to have reduced, what with the introduction of table valued parameters. However, there are a lot of people who don’t want their client-apps changed. Or, they do not want to switch to table-valued parameters due to backward compatibility with older versions of SQL Server. So, here’s how you create your own function to split strings with a delimiter.

 1 CREATE FUNCTION [dbo].[SplitString]
 2 (
 3     @StringToSplit    VARCHAR(8000)= '',
 4     @Delimiter        CHAR(1)= ''
 5 )
 6  RETURNS @ReturnTable TABLE ([Value] VARCHAR(8000) NULL)
 7  AS
 8  BEGIN
 9 
10     -- Return empty table if no string to split is supplied
11      IF ISNULL(LTRIM(RTRIM(@StringToSplit)), '') = ''
12     RETURN
13 
14     -- Return table populated with complete string to split if no delimeter is supplied
15      IF ISNULL(LTRIM(RTRIM(@Delimiter)), '') = ''
16     BEGIN
17 
18         INSERT INTO @ReturnTable
19         SELECT @StringToSplit
20 
21         RETURN
22     END
23 
24     -- Iterate through string to split using delimiter, then return populated table
25      DECLARE @Snippet varchar(8000)
26 
27     WHILE CHARINDEX(@Delimiter, @StringToSplit) <> 0
28     BEGIN
29 
30         SET @Snippet = LEFT(@StringToSplit, CHARINDEX(@Delimiter, @StringToSplit))
31         SET @StringToSplit = RIGHT(@StringToSplit, LEN(@StringToSplit) - LEN(@Snippet))
32 
33         INSERT INTO @ReturnTable
34         SELECT REPLACE(@Snippet, @Delimiter, '')
35     END
36 
37     -- Insert the left over piece of the string to split into the table    
38      IF @StringToSplit <> ''
39         INSERT INTO @ReturnTable
40         SELECT REPLACE(@StringToSplit, @Delimiter, '')
41 
42     RETURN
43  END
44  GO

And this is how you could use the function in your queries:

  • Like this:
1 SELECT *
2  FROM dbo.SplitString('P0032,P8763,P9093', ',')

  • Or like this:
1 SELECT *
2 FROM dbo.Products
3 WHERE [ProductCode] IN (SELECT [Value]
4 FROM dbo.SplitString('P0032,P8763,P8872', ','))

  • Or maybe like this:
1 SELECT P.*
2 FROM dbo.Products P
3 INNER JOIN dbo.SplitString('P0032,P8763,P9093,P_INVALID', ',') S
4 ON P.[ProductCode] = S.[Value]

These would return result sets such as these:

Result 1 Result 2 Result 3
Value
P0032
P8763
P9093
ProductCode Name Price
P0032 Milk 0.50
P8763 Butter 1.10
P8872 Ham 2.75
ProductCode Name Price
P0032 Milk 0.50
P8763 Butter 1.10
P9093 Eggs 0.10