How to use STRING_SPLIT function of SQL Server 2016

Introduction

SQL Server 2016 was released on June 1st, 2016. We can download SQL SERVER 2016 Developer Edition for FREE from Visual Studio Dev Essentials.As like the other versions of it's clan, this time also SQL Server has come up with lot's of new features. In this article we will explore the STRING_SPLIT function.

What is the STRING_SPLIT function ?

As the name suggests, it is a function to split the string using specified delimiter.

The syntax is as under

STRING_SPLIT ( string , separator ) 

where,

string is the string expression being passed

separator is the delimiter.

The STRING_SPLIT function returns a single-column table.

Using the code

Let us first see how we can implement a string splitter in the older versions of SQL server.

Let us say we have a string as 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 and we want to split that based on the delimiter ','.

Case 1: Using XQuery

Let us first convert the string into XML and replace the delimiter with some start and end XML tags .Once done, then by using XQuery we can accomplish our work.

DECLARE @xml xml, @str varchar(100), @delimiter varchar(10)
SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
SET @delimiter = ','
SET @xml = cast(('<X>'+replace(@str, @delimiter, '</X><X>')+'</X>') as xml)
SELECT C.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as X(C)

Case 2: Using Recursive Common Table Expression

DECLARE @str varchar(100), @delimiter varchar(10)
SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
SET @delimiter = ','
;WITH cte AS
(
    SELECT 0 a, 1 b
    UNION ALL
    SELECT b, CHARINDEX(@delimiter, @str, b) + LEN(@delimiter)
    FROM CTE
    WHERE b > a
)
SELECT SUBSTRING(@str, a,
CASE WHEN b > LEN(@delimiter) 
    THEN b - a - LEN(@delimiter) 
    ELSE LEN(@str) - a + 1 END) value      
FROM cte WHERE a > 0

There are multiple other techniques of achieving the same as listed here.

Case 3: Using the new STRING_SPLIT  Of SQL SERVER 2016

SELECT value
FROM STRING_SPLIT('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15', ',') 

In all the above cases, the output will be

N.B.~ The function requires the database to be in compatibility level 130.

References

Conclusion

Hope we have all enjoyed the journey with STRING_SPLIT and understood how it works. Thanks for reading.