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!

CodePudding user response：

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

CodePudding user response：

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