Parse single column into multiple columns using t-sql

Question

Hi All,

I have a table with below data.
ID Notes
1 , Date1: 01/01/2012 | Name: John | Date2: 01/31/2012 | Insurance: Yes | Status: Active
2 , Date1: 01/12/2012 | Name: Danny | | | Status:
3 , Date1: 01/15/2012 | Name: Justin | | | Status: Active
4 , Date1: 01/21/2012 | Name: Kole | Date2: 01/23/2012 | |Status: Active
5 , Date1: 01/21/2012 | Name: Duke | Date2: 01/25/2012 | |

The column Notes stores multiple(5-label/values) with pipeline separated. I want to split Notes column(5 label/values) into multiple columns.
I need output in below format:

ID Date1 Name Date2 Insurance Status
1 01/01/2012 John 01/31/2012 Yes Active
2 01/12/2012 Danny null null null
3 01/15/2012 Justin null null Active
4 01/21/2012 Kole 01/23/2012 null Active
5 01/21/2012 Duke 01/25/2012 null null

DECLARE @alrt Table (ID int , Notes varchar(max))
Insert into @alrt
Values
(1 , 'Date1: 01/01/2012 | Name: John | Date2: 01/31/2012 | Insurance: Yes | Status: Active'),
(2 , 'Date1: 01/12/2012 | Name: Danny | | | Status: ' ),
(3 , 'Date1: 01/15/2012 | Name: Justin | | | Status: Active'),
(4 , 'Date1: 01/21/2012 | Name: Kole | Date2: 01/23/2012 | |Status: Active'),
(5 , 'Date1: 01/21/2012 | Name: Duke | Date2: 01/25/2012 | |')

Thanks in advance,
RH

Answer 1

DECLARE @alrt Table (ID int , Notes varchar(max))
Insert into @alrt
Values
(1 , 'Date1: 01/01/2012 | Name: John | Date2: 01/31/2012 | Insurance: Yes | Status: Active'),
(2 , 'Date1: 01/12/2012 | Name: Danny | | | Status: ' ),
(3 , 'Date1: 01/15/2012 | Name: Justin | | | Status: Active'),
(4 , 'Date1: 01/21/2012 | Name: Kole | Date2: 01/23/2012 | |Status: Active'),
(5 , 'Date1: 01/21/2012 | Name: Duke | Date2: 01/25/2012 | |')

--select * from @alrt

;WITH MultipleColums
AS (
SELECT Notes
,CAST('<x>' + REPLACE(Notes, '|', '</x><x>') + '</x>' AS XML) AS Parts
FROM @alrt
)
SELECT Notes
, Replace ( Parts.value(N'/x[1]', 'varchar(50)'), 'Date1: ','' ) AS [Date1]
,Replace (Parts.value(N'/x[2]', 'varchar(50)') , 'Name: ','' ) as [Name]
,Replace (Parts.value(N'/x[3]', 'varchar(50)') , 'Date2: ','' ) as [Date2]
,Replace (Parts.value(N'/x[4]', 'varchar(50)') , 'Insurance: ','' ) as Name
,Replace ( Parts.value(N'/x[5]', 'varchar(50)') , 'Status: ','' ) as Name
FROM MultipleColums;

Answer 2

Hi @SQL9 ,

Your data resembles JSON, but it is tricky to convert to real JSON due to missing data between the pipes.

Here is a solution similar to @Hafeez Uddin , just more performant and polished.
It is based on XML and XQuery.

SQL

--DDL and sample data population, start  
DECLARE @tbl Table (ID INT IDENTITY PRIMARY KEY, Notes varchar(max))  
INSERT INTO @tbl (Notes) VALUES  
('Date1: 01/01/2012 | Name: John | Date2: 01/31/2012 | Insurance: Yes | Status: Active'),  
('Date1: 01/12/2012 | Name: Danny | | | Status: ' ),  
('Date1: 01/15/2012 | Name: Justin | | | Status: Active'),  
('Date1: 01/21/2012 | Name: Kole | Date2: 01/23/2012 | |Status: Active'),  
('Date1: 01/21/2012 | Name: Duke | Date2: 01/25/2012 | |');  
--DDL and sample data population, end  
  
;WITH rs AS   
(  
 SELECT *  
 , TRY_CAST('<root><x>' + REPLACE(Notes, '|', '</x><x>') + '</x></root>' AS XML) AS xmldata  
 FROM @tbl  
)  
SELECT rs.ID  
 , TRIM(REPLACE(c.value('(x[1]/text())[1]', 'VARCHAR(50)'), 'Date1:', '')) AS [Date1]  
 , TRIM(REPLACE(c.value('(x[2]/text())[1]', 'VARCHAR(50)'), 'Name:', '')) AS [Name]  
 , TRIM(REPLACE(c.value('(x[3]/text())[1]', 'VARCHAR(50)'), 'Date2:', '')) AS [Date2]  
 , TRIM(REPLACE(c.value('(x[4]/text())[1]', 'VARCHAR(50)'), 'Insurance:', '')) AS Insurance   
 , TRIM(REPLACE(c.value('(x[5]/text())[1]', 'VARCHAR(50)'), 'Status:', '')) AS Status  
FROM rs  
 CROSS APPLY xmldata.nodes('/root') AS t(c);  

Output

+----+------------+--------+------------+-----------+--------+  
| ID |   Date1    |  Name  |   Date2    | Insurance | Status |  
+----+------------+--------+------------+-----------+--------+  
|  1 | 01/01/2012 | John   | 01/31/2012 | Yes       | Active |  
|  2 | 01/12/2012 | Danny  | NULL       | NULL      |        |  
|  3 | 01/15/2012 | Justin | NULL       | NULL      | Active |  
|  4 | 01/21/2012 | Kole   | 01/23/2012 | NULL      | Active |  
|  5 | 01/21/2012 | Duke   | 01/25/2012 | NULL      | NULL   |  
+----+------------+--------+------------+-----------+--------+