TSQL split string text column

RJ 106 Reputation points
2023-01-26T18:23:56.6166667+00:00

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.

User's image

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,241 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,561 Reputation points
    2023-01-26T18:50:28.5233333+00:00

    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);
    
    1 person found this answer helpful.

  2. Jingyang Li 5,891 Reputation points
    2023-01-27T18:24:38.7766667+00:00
    
    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;
    
    1 person found this answer helpful.
    0 comments No comments

  3. Jingyang Li 5,891 Reputation points
    2023-01-26T21:15:15.3966667+00:00
    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