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