TSQL split string text column

Question

Hi there,

I'm looking to split a delimited text of a single column into multiple appropriate derived column. The derived columns needs to be dynamic based on the content and need not be in order as abc.. but as long as the parsed string is grouped appropriately into the columns that would be enough.

SQL

create table dbo.t1(
	[id] [int] IDENTITY(1,1) NOT NULL,
	[details] [varchar](255) NULL
	)

insert into dbo.t1 ([details])
values ('d=4; a=1; b=2;e=5'),
('a=1;d=4'),
('c=3; b=2; a=1')

select * from dbo.t1

i have come upto this 

 SELECT t.id, t.details,
  ltrim(rtrim(substring(s.value,1,PATINDEX('%=%',s.value)-1)))  newcol 
,substring(s.value,PATINDEX('%=%',s.value)+1,100) newval
  FROM [VEAR_SA_TEST].dbo.t1 t 
  CROSS APPLY STRING_SPLIT(t.[details], ';') s

Any suggestion how to pivot it as single row?

Thanks a bunch.

Answer 1

Hi @RJ,

Please try the following solution.

-- DDL and sample data population, start
DECLARE @tbl table (id int IDENTITY PRIMARY KEY, details varchar(255) NULL);
INSERT INTO @tbl (details) VALUES 
('d=4; a=1; b=2;e=5'),
('a=1;d=4'),
('c=3; b=2; a=1');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ';';

-- Method #1
select t.*
	, a = SUBSTRING(c.value('(/root/r[substring(text()[1],1,1)="a"]/text())[1]', 'VARCHAR(10)'), 3,10)
	, b = SUBSTRING(c.value('(/root/r[substring(text()[1],1,1)="b"]/text())[1]', 'VARCHAR(10)'), 3,10)
	, c = SUBSTRING(c.value('(/root/r[substring(text()[1],1,1)="c"]/text())[1]', 'VARCHAR(10)'), 3,10)
	, d = SUBSTRING(c.value('(/root/r[substring(text()[1],1,1)="d"]/text())[1]', 'VARCHAR(10)'), 3,10)
	, e = SUBSTRING(c.value('(/root/r[substring(text()[1],1,1)="e"]/text())[1]', 'VARCHAR(10)'), 3,10)
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
      REPLACE(REPLACE(details,SPACE(1),''), @separator, ']]></r><r><![CDATA[') + 
      ']]></r></root>' AS XML)) AS t1(c);

-- Method #2
SELECT t.* 
	, a = JSON_VALUE(j,'$[0].a')
	, b = JSON_VALUE(j,'$[0].b')
	, c = JSON_VALUE(j,'$[0].c')
	, d = JSON_VALUE(j,'$[0].d')
	, e = JSON_VALUE(j,'$[0].e')
FROM @tbl
CROSS APPLY (SELECT TRY_CAST('[{"' + REPLACE(REPLACE(REPLACE(details,SPACE(1),'')
	,';',',"')
	,'=','":') + '}]' AS NVARCHAR(MAX))
) AS t(j);

Answer 2

drop table if exists t1
create table dbo.t1(
	[id] [int] IDENTITY(1,1) NOT NULL,
	[details] [varchar](255) NULL
	)

insert into dbo.t1 ([details])
values ('d=4; a=1; b=2;e=5'),
('a=1;d=4'),
('c=3; b=2; a=1')
,('ac=3; ab=2; aa=1')

select * from dbo.t1

 

 SELECT t.id, t.details,
  ltrim(rtrim(substring(s.value,1,PATINDEX('%=%',s.value)-1)))  newcol 
,substring(s.value,PATINDEX('%=%',s.value)+1,100) newval
 
 
 into 
 atemptable

 FROM dbo.t1 t 
  CROSS APPLY STRING_SPLIT(t.[details], ';') s


declare @ColumnHeaders VARCHAR(MAX) ;
set @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + quotename(newcol,'[') 
                             FROM atemptable
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');
 
 
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 
    'SELECT id, ' + @ColumnHeaders +' FROM atemptable t
     PIVOT
     (
         MAX([newval]) FOR [newcol] IN (' + @ColumnHeaders + ')
     ) p';
print @sql
EXEC(@SQL)

drop table atemptable;

Answer 3

drop table if exists t1;
create table t1 (  details varchar(255) NULL);
insert into dbo.t1 ([details])
values ('d=4;   a=1; b=2;e=5'),
('a=1;d=4'),
('c=3;  b=2; a=1')
;with mycte as (
select *
,' {"'+Replace(Replace(replace([details],' ',''), '=','":"'),';','","') +'"} ' jsonInfo
 
from t1)
 
SELECT   [details],jsonInfo,
--isjson(jsonInfo),
 JSON_VALUE(jsonInfo,'$.a') as [a],
JSON_VALUE(jsonInfo,'$.b') as [b],
JSON_VALUE(jsonInfo,'$.c') as [c],
JSON_VALUE(jsonInfo,'$.d') as [d] ,
JSON_VALUE(jsonInfo,'$.e') as [e] 
from mycte