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!
SQLZealots 