How do I split a delimited string so I can access individual items?

sql

string-parsing

sql-server

performance-tuning

byAnton Shumikhin·Sep 25, 2024

Looking for a way to dissect a string in SQL? SQL Server 2016 and beyond have got you covered with the function STRING_SPLIT():

SELECT value FROM STRING_SPLIT('apple,banana,cherry', ',');

Result:

apple
banana
cherry

If you're working with an older version or a different system, you're likely going to need a custom workaround as the built-in STRING_SPLIT() function might not be available or sufficient.

Workarounds for older SQL Server versions

As the saying goes, where there's a will there's a way. There's always a solution, even when the SQL Server version lacks a native method to split strings.

UDFs: The old reliable

User Defined Functions (UDFs) can be a viable solution for string parsing, especially if you're dealing with specific parsing needs like handling multi-character delimiters.

PARSENAME: A spark of creativity

SELECT PARSENAME(REPLACE('apple orange banana', ' ', '.'), 1);

Sure, it's limited to four parts but hey, not all superheroes wear capes right? Be aware that this trick won't help if your data contains periods.

CHARINDEX and WHILE: The dynamic duo

DECLARE @str NVARCHAR(MAX) = 'apple,orange,banana', @pos INT;

-- Avengers Assemble here
WHILE CHARINDEX(',', @str) > 0
BEGIN
    SELECT @pos  = CHARINDEX(',', @str);  
    SELECT SUBSTRING(@str, 1, @pos-1); -- Up for some substring action
    SET @str = STUFF(@str, 1, @pos, '');
END

Might cause an Infinity War on performance with larger strings, but it does get the job done.

The XML approach: Intelligent play

DECLARE @str NVARCHAR(MAX) = 'apple,orange,banana';
SELECT xmlData.A.value('.', 'NVARCHAR(MAX)') AS value
FROM (SELECT CAST('<M>' + REPLACE(@str, ',', '</M><M>') + '</M>' AS XML) AS string) AS A(xmlData);

Time to show some XML love. Just don't forget to handle those escape sequences.

Open the JSON window: The modern solution

For SQL Server 2016 and beyond, OPENJSON offers a powerhouse performance by treating strings as JSON arrays:

DECLARE @json NVARCHAR(MAX) = '["apple","orange","banana"]';
SELECT value FROM OPENJSON(@json);

Don't want the entire array? JSON_VALUE has got you covered:

DECLARE @json NVARCHAR(MAX) = '["apple","orange","banana"]';
SELECT JSON_VALUE(@json, '$[1]'); -- retrieves 'orange'

Handling edge cases and performance tuning

CTEs: Temporary tables' cool cousin

Common Table Expressions (CTE) can boost performance and code readability compared to traditional temporary tables. SQL Server approves!

`CHARINDEX` and `SUBSTRING`: The precision toolset

These two functions, alongside LTRIM and RTRIM, can significantly streamline your string parsing operation.

RANK(): Ordered and structured

In scenarios where maintaining sequence is important, RANK() combined with a set-based approach offers an effective solution.

ON (MAX) mode: Dealing with large strings

Make sure you're using NVARCHAR(MAX) for large strings, and consider casting to BIGINT when dealing with huge numbers. Your database will thank you.