Development

SQL Server – STRING_SPLIT function in SQL Server 2016

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.

Syntax

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.

stringsplit_1

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),

       MemberName VARCHAR(50),

       Hobbies VARCHAR(50)

)

GO

INSERT INTO ClubInfo (MemberName, Hobbies)

VALUES (‘Kapil’, ‘Cricket,Football,Music’),

              (‘Ankit’, ‘Chess,Billards’)

GO

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

FROM ClubInfo

CROSS APPLY STRING_SPLIT(Hobbies,’,’)

stringsplit_2

Hope you will like this post.

Have a happy learning 🙂

 

 

Advertisements

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