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));
 INSERT INTO @tbl (FullName) VALUES
 ('Vigneshwaran Arunachalam'), 
 ('Murugan Mani'), 
 ('Dharani Kumar Rajput'), 
 ('Uday Talluri'), 
 ('Soundarya Lakshmi'), 
 ('Noname'), 
 ('Latheesh Nedumpurath Kalappurakkal');
 

 -- DDL and sample data population, end
 DECLARE @separator CHAR(1) = SPACE(1);
    
 ;WITH rs AS
 (
  SELECT * 
  , 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;

Result

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));
 INSERT INTO @tbl VALUES 
 (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
 (
  SELECT * 
  , '[{"' + REPLACE(REPLACE(REPLACE(Comments
  , ':', '":"')
  , SPACE(1), '')
  , '||', '","') + '"}]' AS jsondata
  FROM @tbl
 )
 SELECT rs.ID, rs.comments, report.*
 FROM rs
  CROSS APPLY OPENJSON(jsondata)
 WITH 
 (
     [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;

Result

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

Introduction:

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.

Usages:

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.

Limitations:

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.