Exploring SQL Server 2016 T-SQL functions – STRING_SPLIT

SQL Server 2016 has introduced some new T-SQL functions which are very handy to use. I will explore some of them starting with this post.

There are number of questions asked in the forums about splitting the values stored in the form of csv in the column. It has been always not a simple way to do this in SQL Server versions prior to 2016. But in 2016, with the introduction of STRING_SPLIT function this becomes easy. It accepts first parameter as the actual string to be split and second parameter as the delimiter.

Suppose you have a string “This,is,test” which you want to split and have them in separate rows. With STRING_SPLIT you can do it easily as shown below

declare @s varchar(100)
set @s='This,is,test'
select * from STRING_SPLIT(@s,',')

The result is

value
-------------
This
is
test

What if the values are in a column in table and you want split them?

You can use CROSS APPLY operator with this function

declare @products table
(
    product_id int,
    product_names varchar(1000)
)
insert into @products(product_id,product_names)
select 1,'Samsung,LG,Sony' union all
select 2,'Apple,Nokia,Micromax,Xiami'

select
    product_id,value as product_name
from
@products as t1
cross apply
    (
select *
from STRING_SPLIT (t1.product_names,',')
) as t2

The result is

product_id  product_name
----------- -----------------
1           Samsung
1           LG
1           Sony
2           Apple
2           Nokia
2           Micromax
2           Xiami
Advertisements

3 thoughts on “Exploring SQL Server 2016 T-SQL functions – STRING_SPLIT

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s