Splitting Delimited Strings

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
Advertisements