Saturday, October 22, 2016

If You Read One Article About Split String in SQL SERVER 2016s Read this One #3

In the Series of SQL SERVER 2016, this is another post. Before Jumping in detail just think if you have a comma or other separator string and if you have to split it by separator field then for such task  in previous SQL SERVER versions either you will write a function which split the string and return desire values in a column  or
you will use XML function or  might be different custom functions.
Let me explain this with below example. Suppose you have a string like below
DECLARE @FriendList AS VARCHAR(1000)
SET @FriendList ='Ravi,Suyash,Vaibhav,Shyam,Pankaj,Rajul,Javed'

Now you want output like below
String split in SQL SERVER 2016

Then in such cases, you will  follow 2 approaches (their might be other as well)

Approach 1:- Write  a function like below  and use it.
Different ways of spliting a comma seperated string in SQL

And once this function is created you can use like below
custom string split function

Approach 2 :- You can use XML option in SQL SERVER as  shown in below

split string using XML in SQL SERVER

So, the good news is now in SQL SERVER 2016 you don’t need to write  so many lines to split any string. In SQL SERVER 2016 a new string function is Introduced which is
The use of this function is very easy and below is the syntax
STRING_SPLIT (string, separator)
Now, let me show you same output using STRING_SPLIT function
string_split function in SQL SERVER 2016

Isn’t it easy ?
I hope you will like this easy way to split the string.
Provide your feedback.
RJ !!!

1 comment: