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

SQL9 246 Reputation points
2021-05-07T21:17:15.997+00:00

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

1 vote

Accepted answer
  1. Yitzhak Khabinsky 19,936 Reputation points
    2021-05-07T21:58:29.193+00:00

    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     |  
    +----+------------+------------+------------+--------+--------+----------+  
    

0 additional answers

Sort by: Most helpful