T-sql split string based on delimiter

Pre-2016 SQL Server: Splitting strings the hard way

Before SQL Server 2016, there wasn't a built-in function to split strings, but that doesn't mean it was impossible. Oh, no - just a bit more elaborate.

Using CHARINDEX and SUBSTRING

If the built-in STRING_SPLIT is not available, you can still split strings in older versions of SQL Server using other built-in functions:

-- Welcome to the past.
-- Let the split gaming begin!
DECLARE @string NVARCHAR(100) = 'FirstName:LastName',
        @delimiter CHAR(1) = ':';

SELECT CASE 
         WHEN CHARINDEX(@delimiter, @string) > 0 THEN 
           SUBSTRING(@string, 1, CHARINDEX(@delimiter, @string) - 1) 
         ELSE 
           @string 
       END AS FirstName,
       CASE 
         WHEN CHARINDEX(@delimiter, @string) > 0 THEN 
           SUBSTRING(@string, CHARINDEX(@delimiter, @string) + 1, LEN(@string)) 
         ELSE 
           NULL 
       END AS LastName;
-- Null for last name? No problemo.


Multiple delimiters? Meet recursive CTEs

If you're dealing with multiple delimiters, you can use a recursive CTE to spill the beans:

-- Let's play split-the-string bingo!
-- Recursive CTEs, our knights in shining armor
WITH SplitCTE AS (
    SELECT 0 AS StartPos, CHARINDEX(',', @string + ',') AS EndPos
    UNION ALL
    SELECT EndPos + 1, CHARINDEX(',', @string + ',', EndPos + 1)
    FROM SplitCTE
    WHERE EndPos > 0
)
SELECT SUBSTRING(@string, StartPos, EndPos - StartPos) AS Value
FROM SplitCTE
WHERE EndPos > StartPos;
-- It's raining substrings, hal-ale-lujah!


Using LEFT and RIGHT to slice'n'dice

If you know the position of the delimiter, why not use LEFT and RIGHT functions to slice your strings?

-- Slice it like a pro
SELECT LEFT(@string, CHARINDEX(@delimiter, @string) - 1) AS FirstPart,
       RIGHT(@string, LEN(@string) - CHARINDEX(@delimiter, @string)) AS SecondPart;
-- Here, have your slice of data!


The flexibility of UNION ALL

Breaking down long strings into manageable pieces is a breeze with UNION ALL:

-- Welcome to the split string fiesta!
WITH Numbers AS (
    SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) num(n)
),
Sequences AS (
    SELECT a.n + (10 * b.n) + (100 * c.n) AS Number
    FROM Numbers a, Numbers b, Numbers c
)
SELECT SUBSTRING(@string, Number, CHARINDEX(@delimiter, @string + @delimiter, Number) - Number) AS Value
FROM Sequences
WHERE Number <= LEN(@string)
AND SUBSTRING(@delimiter + @string, Number, 1) = @delimiter
-- UNION ALL: Not just an SQL statement, but also a life philosophy. 


Protecting against nulls and empty strings

Always handle potential null or empty values by providing default values where necessary with ISNULL or COALESCE functionalities:

-- Because no one likes NULL surprises
SELECT ISNULL(SUBSTRING(@string, Number, CHARINDEX(@delimiter, @string + @delimiter, Number) - Number), '') AS Value
FROM Sequences
-- Savvy data handling, demo ends here
