Split Delimited String into Columns in SQL Server 2017

Question

I have a table that has following text in a column and I need to convert the texts into multiple columns.

create table Test (
 resource_type varchar(300)
);
insert into Test (resource_type) values
('Number of reservations: 1'),
('Number of reservations: 2  ¶ Perf ID: Event : 51680'),
('Number of reservations: 3  ¶ Perf ID: Event : 51683');

and I have converted this into columns by doing

Select A.*
      ,Pos1 = xDim.value('/x[1]' ,'varchar(100)')  
      ,Pos2 = xDim.value('/x[2]' ,'varchar(100)')
          
From Test A
Cross Apply ( values (convert(xml,'<x>' + replace(A.resource_type,'¶','</x><x>')+'</x>')) )B(xDim)

Output of the code is

Instead, I need Number of reservations and PerfID as columns and under the number of reservations values as 1, 2, and 3 and under perf id null, 51680, and 51683...

Please help me how to proceed further!

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/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 code 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;). All within the question, no images. — Yitzhak Khabinsky, Nov 16 '22 at 22:15
The SQL language as a very strict rule that you need to know about the number and type of columns in the results up front, at query compile stage, **before looking at any data**. If you know you'll have up to 2 or 3 delimiters, you can do this. Otherwise, **you will not be able to do it in a single query**, and must instead use three steps: `1)` Run a query to find how many columns you need. `2)` Use results from 1 to build new SQL on the fly. `3)` Run the query from 2. — Joel Coehoorn, Nov 16 '22 at 22:40
you should read for the future https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — nbk, Nov 16 '22 at 23:19

Yitzhak Khabinsky · Answer 1 · 2022-11-16T22:41:49.250

0

Please try the following solution.

SQL

DECLARE @tbl TABLE (resource_type VARCHAR(300));
INSERT INTO @tbl (resource_type) VALUES
('Number of reservations: 1'),
('Number of reservations: 2  ¶ Perf ID: Event : 51680'),
('Number of reservations: 3  ¶ Perf ID: Event : 51683');

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

SELECT t.* -- , c 
    ,Pos1 = TRIM(c.value('(/root/r[2]/text())[1]' ,'varchar(100)'))
    ,Pos2 = TRIM(c.value('(/root/r[5]/text())[1]' ,'varchar(100)'))
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
      REPLACE(REPLACE(resource_type,'¶',@separator), @separator, ']]></r><r><![CDATA[') + 
      ']]></r></root>' AS XML)) AS t1(c);

Output

resource_type	Pos1	Pos2
Number of reservations: 1	1	NULL
Number of reservations: 2 ¶ Perf ID: Event : 51680	2	51680
Number of reservations: 3 ¶ Perf ID: Event : 51683	3	51683

edited Nov 16 '22 at 22:41

answered Nov 16 '22 at 22:29

Yitzhak Khabinsky

18,471
2
15
21

Thank you this is exactly what I was looking for... But when I use the code and put it as a subquery and try to do the sum of Pos1 column, it throws following error: Msg 8117, Level 16, State 1, Line 36 Operand data type varchar is invalid for sum operator. I tried casting as an integer but still got the same error... – Robin Nov 17 '22 at 00:38
You can put my SELECT statement inside a CTE, and calculate what you need in the SELECT from the CTE – Yitzhak Khabinsky Nov 17 '22 at 01:09

score 0 · Answer 2 · answered Nov 16 '22 at 22:37

0

You could, if you wish, accomplish this just with a bit of charindex / substring, for example:

select resource_type, 
  Substring(resource_type, p1.p, IsNull(NullIf(p2.p,0) - p1.p - 1, Len(resource_type))) Pos1,
  Substring(resource_type, p3.p, Len(resource_type)) Pos2
from test
cross apply(values(CharIndex(':', resource_type) + 2))p1(p)
cross apply(values(CharIndex('¶', resource_type) ))p2(p)
cross apply(values(NullIf(CharIndex('Event', resource_type, p1.p), 0) + 8))p3(p);

Demo Fiddle

answered Nov 16 '22 at 22:37

Stu

30,392
6
14
33

Thank you this is exactly what I was looking for... But when I use the code and put it as a subquery and try to do the sum of Pos1 column, it throws following error: Msg 8117, Level 16, State 1, Line 36 Operand data type varchar is invalid for sum operator. I tried casting as an integer but still got the same error... – Robin Nov 17 '22 at 00:39
The result of splitting a string is still a string, you will need to *cast* it first if the intention is to aggregate, eg wrap the column with `try_convert(int, pos1)` – Stu Nov 17 '22 at 08:42

John Cappelletti · Answer 3 · 2022-11-16T23:51:00.383

0

Since you are running 2016+ you can use a bit of JSON. The JSON approach is FAR MORE PERFORMANT than the XML approach (which looks very familiar :) )

Select A.resource_type
      ,Pos1 = trim(JSON_VALUE(JS,'$[1]'))
      ,Pos2 = trim(JSON_VALUE(JS,'$[4]'))
 From  Test A
 Cross Apply (values ('["'+replace(string_escape(replace(resource_type,'¶',':'),'json'),':','","')+'"]') ) B(JS)

Results

edited Nov 16 '22 at 23:51

answered Nov 16 '22 at 23:13

John Cappelletti

79,615
7
44
66

Thank you this is exactly what I was looking for... But when I use the code and put it as a subquery and try to do the sum of Pos1 column, it throws following error: Msg 8117, Level 16, State 1, Line 36 Operand data type varchar is invalid for sum operator. I tried casting as an integer but still got the same error... – Robin Nov 17 '22 at 00:39
@Robin You will have to convert to INT for example: ,Pos1 = try_convert(int,trim(JSON_VALUE(JS,'$[1]'))) Keep the TRIM in there otherwise it may fail. – John Cappelletti Nov 17 '22 at 01:10

Split Delimited String into Columns in SQL Server 2017

3 Answers3