Split data based on pipeline character in between a string using t-sql

Question

Hi All,

I want to split data based on pipeline character("||") with in the string using t-sql.

source column data looks like in below format.

ID, Comments
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

I want t-sql code to show output in below format

ID, StartDate EndDate Position Salary Age IsActive
1 01/01/2000 01/31/2001 Manager 100K 50 No
2 01/01/2002 null Sr.Manager 150K 55 Yes
3 01/01/2001 null Director null 55 Null

Sample data:

declare @table table (ID int, Comments varchar(500))
INSERT INTO @table VALUES (1, 'StartDate: 01/01/2000 || EndDate: 01/31/2001 || Position: Manager || Salary: 100K || Age:50 || IsActive: No')
INSERT INTO @table VALUES (2, 'StartDate: 01/01/2002 || Position: Sr.Manager || Salary: 150K || Age:55 || IsActive: Yes')
INSERT INTO @table VALUES (3, 'StartDate: 01/01/2001 || Position: Director || Age:55' )

Thanks in advance
RH

Answer 1

Hi @SQL9 ,

Please try the following solution.
It will work starting from SQL Server 2016 onwards.

The data closely resembles JSON. That's is why it is a two step process:

1. Data conversion to JSON.
2. JSON conversion to rectangular/relational format.

SQL

-- 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, 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;  

Output

+----+------------+------------+------------+--------+--------+----------+  
| ID | StartDate  |  EndDate   |  Position  | Salary | Age    | IsActive |  
+----+------------+------------+------------+--------+--------+----------+  
|  1 | 01/01/2000 | 01/31/2001 | Manager    | 100K   |     50 | No       |  
|  2 | 01/01/2002 | NULL       | Sr.Manager | 150K   |     55 | Yes      |  
|  3 | 01/01/2001 | NULL       | Director   | NULL   |     55 | NULL     |  
+----+------------+------------+------------+--------+--------+----------+