Tag: split string in sql server

Few use cases of splitting strings in SQL Server

Its a very frequent question in forums on splitting strings with various forms of data. This blog post will try to get few of use cases and provide solutions to those scenarios in a simpler way.

Case 1: Split a full name to first name & last name

This case is a very common one scenario, a full name in a column needs to be split into two as First Name and Last Name.

-- DDL and sample data population, start
 DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY,  FullName varchar(200));
 ('Vigneshwaran Arunachalam'), 
 ('Murugan Mani'), 
 ('Dharani Kumar Rajput'), 
 ('Uday Talluri'), 
 ('Soundarya Lakshmi'), 
 ('Latheesh Nedumpurath Kalappurakkal');

 -- DDL and sample data population, end
 DECLARE @separator CHAR(1) = SPACE(1);
 ;WITH rs AS
  , TRY_CAST('<root><r>' + 
  REPLACE(FullName, @separator, '</r><r>') + 
  '</r></root>' AS XML) AS xmldata
  FROM @tbl
 SELECT ID, rs.FullName 
  , rs.xmldata.value('(/root/r[1]/text())[1]', 'VARCHAR(30)') AS firstName
  , rs.xmldata.query('data(/root/r[position() gt 1]/text())').value('.' , 'VARCHAR(100)') AS lastName
 FROM rs;


Case 2: Multi split a string to columns

This is a multi split scenario, where you would like to divide your single string into multiple columns according to the key name in the string. if there are no key then it has to consider NULL value while preparing in a tabular format.

-- DDL and sample data population, start
 DECLARE @tbl table (ID int, Comments VARCHAR(500));
 (1, 'StartDate: 01/01/2000 || EndDate: 01/31/2001 || Position: Manager || Salary: 100K || Age:50 || IsActive: No'),
 (2, 'StartDate: 01/01/2002 || Position: Sr.Manager || Salary: 150K || Age:55 || IsActive: Yes'),
 (3, 'StartDate: 01/01/2001 || Position: Director || Age:55');
 -- DDL and sample data population, end
 ;WITH rs AS
  , '[{"' + REPLACE(REPLACE(REPLACE(Comments
  , ':', '":"')
  , SPACE(1), '')
  , '||', '","') + '"}]' AS jsondata
  FROM @tbl
 SELECT rs.ID, rs.comments, report.*
 FROM rs
     [StartDate] VARCHAR(10) '$.StartDate'
     , [EndDate] VARCHAR(10) '$.EndDate'
     , [Position] VARCHAR(30) '$.Position'
     , [Salary] VARCHAR(10) '$.Salary'
     , [Age] INT '$.Age'
     , [IsActive] VARCHAR(3) '$.IsActive'
 ) AS report;


I will be adding more cases as I come across in future. I would like to request you to share your thoughts on any alternatives and any new use cases you would like to add to this list.

See Also:

String_Split function in SQL ServerĀ 2016

If you enjoyed this blog post, feel free to share it with your friends!

String_Split function in SQL Server 2016


SQL Server 2016 has introduced a built-in table valued function string_split to split a string into rows based on the separator.

Earlier, developers need to write custom/user defined function to do the split work. However, with the new function, its a built-in and can be used directly. The problem with user defined function is the way the function is defined and used.There are multiple ways usually developers write custom functions like scalar/Table valued functions. And its observed in many cases, scalar functions are not scaling as desired and causing serious performance issues.


1. Split the variable string

Select * From string_split('first,second,third',',')

2. Split the table rows with the help of CROSS APPLY

Create Table splitTable(Col int, string varchar(100))
Insert into splitTable values(1,'First,Second'),(2,'Primary,Secondary')

Select * From splitTable a
Cross apply string_split(a.string,',') B

Drop Table splitTable

If we analyse the execution plan of the above query, we can see “Table Valued Function” operator is used to implement the function.


1. ONLY a single-character delimiters can be used at a time. If you want to apply multiple delimiter, you can use multiple CROSS APPLY.

2. There is no way to understand the ordinal position/eliminate duplicates/empty strings in the string.

3. This function will work ONLY if the compatibility of your database is 130 or later irrespective of SQL Server version.

I would recommend to use this built-in function for future development and provide your experience as part of learning and sharing.