How to use STRING_SPLIT function of SQL Server 2016

Rajnilari2015
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 865 red flag

SQL Server 2016 was released on June 1st, 2016.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.

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

STRING_SPLIT (Transact-SQL)

Conclusion

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

Page copy protected against web site content infringement by Copyscape

About the Author

Rajnilari2015
Full Name: Niladri Biswas (RNA Team)
Member Level: Platinum
Member Status: Member,Microsoft_MVP,MVP
Member Since: 3/17/2015 2:41:06 AM
Country: India
-- Thanks & Regards, RNA Team


Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)