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!

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 )

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