Split function equivalent in T-SQL?

The Nitty-Gritty of string splitting

Your XML guide to string splitting

Press XML into service. It replaces your string into an XML format, each element symbolizing segments between delimiters. The nodes method then plays splitter.

Custom delimiters: "no problem!"

REPLACE function lets you swap your custom delimiter with a standard one. Then split strings effortlessly with XML conversion.

-- When '|custom|' delimiters think they're unique and irreplaceable.
DECLARE @input VARCHAR(MAX) = 'word1|custom|delimiter|word3'
SELECT Split.a.value('.', 'VARCHAR(MAX)') AS Value
FROM (SELECT CAST ('<X>' + REPLACE(@input, '|custom|', '</X><X>') + '</X>' AS XML) AS Data) AS A 
CROSS APPLY Data.nodes ('/X') AS Split(a)


Taking on Godzilla-sized datasets

For splitting big datasets, common table expressions (CTE) or table variables are your safe bets. Not only can you reuse the output for complex queries, but it also saves you from repeatedly running the split mechanics.

CTE: Better code, happier life

CTE can simplify and beautify your split code, making it readable and maintainable. Use it for elegant handling of recursive or complex string manipulations.

The Grand Tour of dragon slaying techniques

.NET for extra firepower (SQL Server 2008)

Bump up your game, .NET code is here. Although it requires extra-lifting (CLR integration), it offers swift performance for complex operations or large data volumes.

STRINGS_SPLIT and TRY_CAST: Safety first!

STRING_SPLIT is a quick and easy path to string splitting since SQL Server 2016. But safety comes first. TRY_CAST ensures that your data's type integrity is intact.

-- Safety goggles on! Only numbers, please
SELECT TRY_CAST(value AS INT) AS Number
FROM STRING_SPLIT('10, ABC, 20', ',')
WHERE TRY_CAST(value AS INT) IS NOT NULL


For when order matters

STRING_SPLIT does not promise order. For keeping the split order, add an IDENTITY property when inserting into a table:

-- When your boss is a tad too obsessed with "order"
DECLARE @TempTable TABLE (ID INT IDENTITY(1,1), value VARCHAR(255))

INSERT INTO @TempTable (value)
SELECT value FROM STRING_SPLIT('one,two,three', ',')

SELECT ID, value FROM @TempTable


Expert Advice

Aaron Bertrand, the SQL maestro, offers invaluable insights into a comparison of string splitting methods, which can help you wisely pick the one saving your time and server load.