Hi friends, in SQL server 2016 another new function STRING_SPLIT gets introduced which helps in splitting the character expression using separator.
Previously, to do this type of work we need to write some code or function to split the character expression but in SQL 2016 function STRING_SPLIT reduce that work to write multiple lines of code or function.
STRING_SPLIT (String, Separator)
Here, String parameter can be of any character type and Separator parameter is a single character expression.
String_Split function requires atleast compatibility level 130. If you are using any lower version then you need to change the compatibility level to 130.
Let’s see some examples of how String_Split works.
Here, I have declared a string variable and stored a value with few ‘@’ sign. Now, I need to write down this string into multiple rows within a single column separated by ‘@’.
Declare @m varchar(20) = ‘a@b@c’
SELECT value from STRING_SPLIT(@m, ‘@’);
Here, value is the name of the column.
If you will pass the NULL value to the string result set will be empty. Also, if you passes any blank values in the string then it will results in empty row.
Declare @strn varchar(10) = NULL
SELECT * FROM STRING_SPLIT(@strn, ’,’);
It will results in empty result set.
Let’s see another example. I have created a table ‘ClubInfo’ and populated it with some data.
CREATE TABLE ClubInfo
MemberID INT Identity(1,1),
INSERT INTO ClubInfo (MemberName, Hobbies)
VALUES (‘Kapil’, ‘Cricket,Football,Music’),
Here, Hobbies column contains multiple hobbies of a person separated by comma.
Now, using STRING_SPLIT we will separate the hobbies in multiple rows.
SELECT MemberName, MemberID, VALUE as Hobby
CROSS APPLY STRING_SPLIT(Hobbies,’,’)
Hope you will like this post.
Have a happy learning 🙂