question

SQL9-3412 avatar image
1 Vote"
SQL9-3412 asked SQL9-3412 commented

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

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is your SQL Server version?

0 Votes 0 ·

1 Answer

YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered SQL9-3412 commented

Hi @SQL9-3412,

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     |
 +----+------------+------------+------------+--------+--------+----------+
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks YitzhakKhabinsky-0887. It worked as expected. Thank you very much for your help.

0 Votes 0 ·