在SQL Server 2017中将分隔字符串拆分为列

w46czmvw 于 2022-11-21 发布在 SQL Server

关注(0)|答案(3)|浏览(213)

我有一个表，其中一列中有以下文本，我需要将这些文本转换为多列。

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

我通过执行以下操作将其转换为列

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)

代码的输出为

相反，我需要保留数和PerfID作为列，保留数的值为1、2和3，perf id为空、51680和51683...
请帮助我如何进一步进行！

sql-server

来源：https://stackoverflow.com/questions/74467843/split-delimited-string-into-columns-in-sql-server-2017

3条答案

按热度按时间

5m1hhzi41#

请尝试以下解决方案。

查询语句

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

输出

| 资源类型|位置1|位置2|
| - -|- -|- -|
| 保留数：1|一个|空值|
| 保留数：2 ¶穿孔ID：事件编号：51680| 2个|小行星51680|
| 保留数：3 ¶穿孔ID：事件编号：51683|三个|小行星51683|

赞(0）回复(0）举报 2022-11-21

z9smfwbn2#

如果您愿意，可以使用一点 charindex / substring 来完成此操作，例如：

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

赞(0）回复(0）举报 2022-11-21

ddrv8njm3#

因为你运行的是2016+，所以你可以使用一点JSON。JSON方法比XML方法（看起来非常熟悉：））FAR MORE PERFORMANT

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)

结果

赞(0）回复(0）举报 2022-11-21

我来回答

在SQL Server 2017中将分隔字符串拆分为列

3条答案

相关问题

热门标签

最新问答