TSQL split string text column

Question

TSQL split string text column

RJ 366

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.

Yitzhak Khabinsky 27,106 Reputation points

2023-01-26T18:32:45.83+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)

3 answers

Your answer

Yitzhak Khabinsky 27,106 Reputation points

2023-01-26T18:32:45.83+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)

Answer 1


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 2

Yitzhak Khabinsky 27,106

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);

RJ 366 Reputation points

2023-01-26T19:14:07.5366667+00:00
Thanks I did add SQL to reproduce of what i tried. Not sure if there was a delay or timing coincidence.

Can you please suggest how do i pivot this query

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 dbo.t1 t CROSS APPLY STRING_SPLIT(t.[details], ';') s
RJ 366 Reputation points

2023-01-26T19:14:47.28+00:00

abc is can not be hardcoded since i might get x y z as well.
Yitzhak Khabinsky 27,106 Reputation points

2023-01-26T19:18:20.89+00:00

It is better not to pivot anything.

Both XML and JSON are using idea of tokenization. NO need to rotate rows and columns, pivot, unpivot, etc.
Yitzhak Khabinsky 27,106 Reputation points

2023-01-26T19:30:10.1366667+00:00

abc is can not be hardcoded since i might get x y z as well.

In such case just dynamic SQL would work for you.

Answer 3

Jingyang Li 5,901 Volunteer Moderator

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

RJ 366 Reputation points

2023-01-27T17:05:18.51+00:00
Thanks @jingyang li

The abc values are dynamic might get x, y,AA also in future. so prefer not to hardcode. I was able to split them down below however is there a way to pivot (newcol) from here.

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

Share via

TSQL split string text column

3 answers

Your answer