Posted in 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.


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


It will results in empty result set.

Let’s see another example. I have created a table ‘ClubInfo’ and populated it with some data.



       MemberID INT Identity(1,1),

       MemberName VARCHAR(50),

       Hobbies VARCHAR(50)



INSERT INTO ClubInfo (MemberName, Hobbies)

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

              (‘Ankit’, ‘Chess,Billards’)


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



Hope you will like this post.

Have a happy learning 🙂





I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s