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