T-sql split string

sql

string-split

sql-server

delimiter

byNikita Barsukov·Sep 30, 2024

In SQL Server 2016+, use STRING_SPLIT to split a string into rows:

SELECT value FROM STRING_SPLIT('item1,item2,item3', ',');

This artery slicer chops your string into bite-sized rows. If you're stuck in the past with an older version, you'll need a combo of SUBSTRING and CHARINDEX or a custom function. Don't worry, we have covered that for you as well.

Pre-2016 options

The XML Parses it all

In SQL Server 2008 R2, the XML approach pulls through:

DECLARE @Input VARCHAR(MAX) = 'item1,item2,item3';
SELECT Split.a.value('.', 'VARCHAR(100)') AS Value 
FROM (SELECT CAST ('<M>' + REPLACE(@Input, ',', '</M><M>') + '</M>' AS XML) AS Data) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a);  --'Because nodes understand splits better than humans'

Your CSV just got an XML makeover. The CROSS APPLY method is the fashion police here, breaking the style-apart gaudy string into elegant rows.

Loopy Delimit-Inator

Or create a custom loop-master function to demarcate territory between delimit-able parts. Here’s an example:

CREATE FUNCTION dbo.SplitString (@Input VARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @Output TABLE (Value VARCHAR(MAX))
AS ..
BEGIN
  ..
  WHILE CHARINDEX(@Delimiter, @Input) > 0 --'Because commas are too mainstream'
  ..
END

Call this function into action with:

SELECT Value FROM dbo.SplitString('item1,item2,item3', ',');

This function is basically a drooling baby, nibbling chunks of the input and spitting them into the output table.

Edge cases and optimization techniques

Space, the final frontier

Use spaces trimmers LTRIM and RTRIM to avoid choking on air:

SELECT LTRIM(RTRIM(value)) AS TrimmedValue FROM STRING_SPLIT('item1 , item2 , item3', ',');

Recursive CTEs and the Art of Recursion

Going with recursive CTEs? Let SQL Server know you're unlimited with OPTION (MAXRECURSION 0):

;WITH cte AS (...)
SELECT * FROM cte OPTION (MAXRECURSION 0);

Speed Breakers

Set-based approaches are the F1 racers of SQL splitting. BYTE into gummy VARCHAR(MAX) data knowing that STRING_SPLIT is faster than other custom or XML-based methods.

Play-doh delimiters

Replace Function

Got dirty delimiters? Clean them up with the REPLACE function before chucking them in the splitinator:

SELECT value FROM STRING_SPLIT(REPLACE('item1|item2|item3', '|', ','), ',');

XML Method

XML method? No fret! Delimiter replacement is just an amendment in the REPLACE function:

DECLARE @Input VARCHAR(MAX) = 'item1|item2|item3';
SELECT Split.a.value('.', 'VARCHAR(100)') AS Value 
FROM (SELECT CAST ('<M>' + REPLACE(@Input, '|', '</M><M>') + '</M>' AS XML) AS Data) AS A 
CROSS APPLY Data.nodes ('/M') AS Split(a);