Parse data with JSON

Parse data with JSON.sql

Declare @rept  table(Id  int Identity(1,1), ReptDesc varchar(max))
Insert into @rept (ReptDesc)
Values(
'Category: Projects; Accounting and Operations; Sales 
Description: this is a transaction report'),
('Category: Technical, Accounting
Description:'),
('Category: Accounting'),
('Category: Accounting, Sales'),
(Null),
(Null)


--SQL Server 2016 or up versions
;with mycte as (
Select * , replace(replace(replace(replace(replace(replace(ReptDesc,char(10),''),char(13),''),
'Category:','{"Category":["'),',','","'), ';','","'),'Description:','"], "Description":["')+'"]}' jsonCol 
from @rept 
 )

 select id 
 ,c.value  as Category  
 ,d.value  as Description   
 from mycte
 CROSS APPLY OPENJSON(jsoncol, '$.Category') AS c
 Outer APPLY OPENJSON(jsoncol, '$.Description') AS d
  
/*
id	Category	Description
1	 Projects	 this is a transaction report
1	 Accounting and Operations	 this is a transaction report
1	 Sales 	 this is a transaction report
2	 Technical	
2	 Accounting	
3	 Accounting	NULL
4	 Accounting	NULL
4	 Sales	NULL
*/