时间戳

首页 > 解决方案 > 将一列不规则列拆分为多列

sql - 将一列不规则列拆分为多列

问题描述

我需要使用各种分隔符 SQL Server 将一个不规则列拆分为多个子列。

我有一个名为 Event_Name 的列，其中包含以下不规则数据：

EVENT_NAME
----------------
ABBRV
Noun Noun2 Noun3 - Adjective - MM/DD/YYYY - LOCATION
Noun Noun2 - MM/DD/YYYY (#1) - LOCATION 
Noun Noun2 - MM/DD/YYYY - Adjective (#1) - LOCATION
Noun, Noun1a Noun2 Noun3 - Adjective: MM/DD/YYYY - Adjective2 - LOCATION

like:
"QRCC"
"Pool Party Dance - Late Night - 12/12/2020 - North"
"Lawn Bowling - 12/12/2020 (#1) - South "
"Lawn, Pool Class Signups - Early: 12/12/2020 - Canceled - North" 
"Pool Event - 11/31/2020 - To Be Announced (#1) - South"

我以前尝试使用 Python 解决这个问题，但是现在，管道的结构化方式，我真的需要在 SQL 查询中进行拆分并使用嵌入的日期作为条件。

在 Python 中，我正在研究类似的东西

new= df['Event_Name'].str.split(" ",n=2, expand = True)
new[3] = new[2].str.split("-", expand= True)[1]
new[4] = new[2].str.split("-", expand= True)[2]
new[5] = new[2].str.split("-", expand= True)[3]

new[3] = new[3].str.split(' ()', expand=True)[2]

new[5]=new[2].str[-4:]
data = new[[0,1,3,5]]

0    |  1    | 3          |  5
-------------------------------
Noun | Noun2 | xx/xx/xxxx |  LOCATION

但这并没有考虑到形容词，也没有在极少数情况下捕捉到"Noun, Noun1a Noun2 Noun3 - Adjective: MM/DD/YYYY - Adjective - LOCATION" 格式，输出应该是

0            |  1   | 3          |  5
----------------------------------------------
Noun, Noun1a | Noun2|  xx/xx/xxxx|  LOCATION

所以实际所需的输出将是

(Noun and Noun1a if not null) or ABBRV | Noun2 or null | Noun3 or null | DATE | Adjective or null| Adjective 2 or null| LOCATION  

OR

Event Cat | Detail | Detail | DATE       | Status     | Status  | LOCATION
-------------------------------------------------------------
QRCC
Pool      | Party   | Dance  | 12/12/2020 | Late Night |         | North
Lawn      | Bowling |        | 12/12/2020 |            |         | South 
Lawn, Pool| Class   | Signups| 12/12/2020 | Early      |Canceled |North 
Pool      |Event    |        |11/31/2020  | To Be Announced |    | South

偶尔"(#1)"是无关紧要的，可以省略。作为 SQL 调用的一部分，我怎样才能做到这一点？

标签： sqlsql-servertsqlsplit

解决方案

老实说：这个设计很糟糕。任何改变输入的机会都比处理这个要好。这意味着：仅当您无法更改获取数据的方式时，才使用我的建议来解析它。但有时我们不得不处理垃圾......

DECLARE @tbl TABLE(ID INT IDENTITY, YourString VARCHAR(1000));
INSERT INTO @tbl VALUES
 ('QRCC')
,('Pool Party Dance - Late Night - 12/12/2020 - North')
,('Lawn Bowling - 12/12/2020 (#1) - South')
,('Lawn, Pool Class Signups - Early: 12/12/2020 - Canceled - North') 
,('Pool Event - 11/31/2020 - To Be Announced (#1) - South');

--查询

SELECT TheFirstFragment.value('/x[1]','nvarchar(max)') AS [Event Cat]
      ,TheFirstFragment.value('/x[2]','nvarchar(max)') AS [Detail]
      ,TheFirstFragment.value('/x[3]','nvarchar(max)') AS [Detail]
      ,AssumablySomeDate.value('/x[contains(.,"/")][1]','nvarchar(max)') AS HopefullyTheDate
      ,AssumablySomeDate.value('/x[not(contains(.,"/"))][1]','nvarchar(max)') AS [Status1]
      ,CASE WHEN TheThirdFragment NOT IN('North','South') AND TheThirdFragment NOT LIKE '%/%' THEN TheThirdFragment END AS [Status2]
      ,CASE WHEN YourStringAsXml.value('count(/x)','int')>2 THEN YourStringAsXml.value('/x[last()]','nvarchar(max)') END AS [LOCATION]
FROM @tbl t
CROSS APPLY(SELECT CAST('<x>' + REPLACE((SELECT t.YourString AS [*] FOR XML PATH('')),' - ','</x><x>') + '</x>' AS XML)) A(YourStringAsXml)
OUTER APPLY(SELECT Cast('<x>' + REPLACE((SELECT REPLACE(YourStringAsXml.value('/x[1]','nvarchar(max)'),', ',',') AS [*] FOR XML PATH('')),' ','</x><x>') + '</x>' AS XML)) B(TheFirstFragment)
OUTER APPLY(SELECT CAST('<x>' + REPLACE((SELECT YourStringAsXml.value('/x[contains(.,"/")][1]','nvarchar(max)') AS [*] FOR XML PATH('')),' ','</x><x>') + '</x>' AS XML)) C(AssumablySomeDate) 
OUTER APPLY(SELECT YourStringAsXml.value('/x[3]','nvarchar(max)')) AS D(TheThirdFragment);

简而言之，这个想法

s将APPLY预先计算一些列

  • 我们使用 XML 在空白破折号空白处将其拆分为片段
  • 我们将第一个片段作为在单个空格处重复拆分（但我们必须先处理逗号空格）
  • 我们在片段中寻找那个。其中包含一个斜线并将其拆分为单个空白。
  • 我们按原样选择第三个片段

栏目列表

  • 选取第一个片段的第 1、第 2 和第 3 部分
  • 包含斜线的部分可能是日期（但请仔细查看 2020 年 11 月 31 日！）
  • 不包含斜线的部分可能是“Early”之类的附加信息
  • 第三个片段可能是状态，如果它既不是北，也不是南，也不包含斜线。
  • 并且 - 如果有超过 2 个片段，最后一个应该是位置。

使用可能并且应该清楚地表明，这可能随时中断......

推荐阅读