sql-server - SQL Server - 将字段数据解析为单独的列的函数

Question

我已经查看了大约 8 个与此类似的线程，但没有一个能够满足我的确切需求(此处相关列中缺乏分隔符一致性)，因此请不要在没有重复的情况下将其标记为可能重复充分阅读并理解我的要求。

Azure SQL Server 2019:

我继承了一个名为 dbo.Table 的表，其中包含数百万条记录，如下所示:

Id   Body
1    Status: Completed
     Successful actions count: 106
     Page load count: 105

2    Status: Failed
     Successful actions count: 91
     Page load count: 90

3    Status: Completed
     Successful actions count: 44
     Page load count: 32

我知道(并且对此感到恼火)这种结构并不理想。我需要修复它，并且非常感谢任何朝着正确方向提供的建议:

我已向表中添加了三列:Status、Successful_Actions_Count 和 Page_Load_Count。

将数据从 Body 列解析到三个新列(无论是现有数据还是将来的插入)的最佳方法是什么？

我不是在找人为我编写存储过程。相反，我可以使用哪些 SQL Server 函数来促进这一点，并且最好通过现有的存储过程和 future 的触发器来实现这一点吗？

我正在查看STRING_SPLIT，但这似乎适用于逗号分隔的字符串(或其他分隔符)。由于我有限的 SQL 技能，我面临的挑战是没有一致的分隔符可供使用 - 空格显然不起作用。如果 SQL 专家有任何想法，我们将不胜感激。

编辑1:我使用下面的 @Zhorov 解决方案来以我们需要的方式显示结果，但这在触发器内不起作用，因此我们实际上可以将结果写入记录。

这是触发器创建语句 - 我仅使用其中一列对其进行测试:

CREATE TRIGGER [dbo].[BodyParseTrigger] on [dbo].[MailArchive]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.MailArchive (
 Status
)
SELECT 
j.*
FROM INSERTED
CROSS APPLY OPENJSON (CONCAT('{"', REPLACE(REPLACE(Mail_Body, ': ', '":"'), CHAR(10), '","'), '"}')) 
WITH (
   Status varchar(100) '$.Status'
     ) j
SET NOCOUNT OFF
END

创建命令成功完成。但是，每当表上发生 INSERT 操作时，我都会收到一条错误消息，完全阻止 INSERT 操作。消息是:

JSON text is not properly formatted. Unexpected character "'" is found at position 10.

这是触发器要克服的第一个问题 - 一旦我弄清楚了这一点，我还需要了解如何放入多个OPENJSON语句以覆盖所有 3 列。按照 @Zhorov 答案中的方式编写它，使其仅作为一个值插入一个项目，而触发器需要 3 个项目。

Answer 1

原始答案:

另一种可能的方法是 JSON 转换(正如@PanagiotisKanavos 在评论中提到的)。您需要将 Body 数据转换为有效的 JSON 对象，并使用 OPENJSON() 和显式架构解析该对象:

表:

CREATE TABLE Data (
   Id int,
   Body varchar(max) 
)
INSERT INTO Data
   (Id, Body)
VALUES
   (1, 'Status: Completed' + CHAR(13) + CHAR(10) + 'Successful actions count: 106' + CHAR(13) + CHAR(10) + 'Page load count: 105'),
   (2, 'Status: Failed' + CHAR(13) + CHAR(10) + 'Successful actions count: 91' + CHAR(13) + CHAR(10) + 'Page load count: 90'),
   (3, 'Status: Completed' + CHAR(13) + CHAR(10) + 'Successful actions count: 40' + CHAR(13) + CHAR(10) + 'Page load count: 44')

声明:

SELECT j.*
FROM Data d
CROSS APPLY OPENJSON (CONCAT('{"', REPLACE(REPLACE(d.Body, ': ', '":"'), CHAR(13) + CHAR(10), '","'), '"}')) WITH (
   Status varchar(100) '$.Status',
   Successful_Actions_Count int '$."Successful actions count"',
   Page_Load_Count int '$."Page load count"'
) j

结果:

-------------------------------------------------------
Status      Successful_Actions_Count    Page_Load_Count
-------------------------------------------------------
Completed   106                         105
Failed      91                          90
Completed   40                          44         

如果 Body 列中有 NULL 值，您可以尝试使用如下方法:

SELECT d.Id, j.*
FROM Data d
OUTER APPLY OPENJSON (
   CASE 
      WHEN d.Body IS NULL THEN '{}'
      ELSE CONCAT('{"', REPLACE(REPLACE(d.Body, ': ', '":"'), CHAR(13) + CHAR(10), '","'), '"}')
   END   
) WITH (
   Status varchar(100) '$.Status',
   Successful_Actions_Count int '$."Successful actions count"',
   Page_Load_Count int '$."Page load count"'
) j

如果 Body 列中的数据以换行符结尾，您需要添加一个额外的 key:value 对 ("x": "0") 使 JSON 有效:

SELECT d.Id, j.*
FROM Data d
OUTER APPLY OPENJSON (
   CASE 
      WHEN d.Body IS NULL THEN '{}'
      ELSE CONCAT('{"', REPLACE(REPLACE(d.Body, ': ', '":"'), CHAR(13) + CHAR(10), '","'), 'x": "0"}')
   END   
) WITH (
   Status varchar(100) '$.Status',
   Successful_Actions_Count int '$."Successful actions count"',
   Page_Load_Count int '$."Page load count"'
) j

更新:

如果您想实现触发器(我认为您需要不同类型的触发器)，接下来的代码行可能会有所帮助。

表和触发器:

CREATE TABLE MailArchive (
   Id int,
   Mail_Body varchar(max),
   Status varchar(100),
   Successful_actions_count int,
   Page_load_count int
);
CREATE TRIGGER BodyParseTrigger ON MailArchive INSTEAD OF INSERT
AS BEGIN
   INSERT INTO MailArchive (ID, Mail_Body, Status, Successful_Actions_Count, Page_Load_Count)
   SELECT i.ID, i.Mail_Body, j.Status, j.Successful_Actions_Count, j.Page_Load_Count
   FROM Inserted i
   OUTER APPLY OPENJSON (CONCAT('{"', REPLACE(REPLACE(i.Mail_Body, ': ', '":"'), CHAR(13) + CHAR(10), '","'), '"}')) 
   WITH (
      Status varchar(100) '$.Status',
      Successful_Actions_Count int '$."Successful actions count"',
      Page_Load_Count int '$."Page load count"'
   ) j
END

声明:

INSERT INTO MailArchive
   (Id, Mail_Body)
VALUES
   (1, 'Status: Completed' + CHAR(13) + CHAR(10) + 'Successful actions count: 106' + CHAR(13) + CHAR(10) + 'Page load count: 105')

SELECT *
FROM MailArchive

结果:

---------------------------------------------------------------------------------------
Id  Mail_Body                     Status      Successful_actions_count  Page_load_count
---------------------------------------------------------------------------------------
1   Status: Completed             Completed   106                         105
    Successful actions count: 106
    Page load count: 105    

如何删除额外的换行符:

如果您的 Mail_Body 列包含额外的换行符，您可以尝试更改转换以消除可能的 JSON 解析错误。现在，转换的结果将是一个 JSON 数组 (["Status: Completed", ...])，而不是 JSON 对象 ({"Status":"Completed", . ..})。在这种情况下，您应该将 OPENJSON() 与默认架构一起使用(不带 WITH 子句)并使用 MAX() 来获得预期结果:

带有额外换行符的表格和数据:

DECLARE @text1 varchar(max) = 
   'Status: Completed' + CHAR(13) + CHAR(10) + 
   'Successful actions count: 106' + CHAR(13) + CHAR(10) + 
   'Page load count: 105' + CHAR(13) + CHAR(10) +
   CHAR(13) + CHAR(10) +
   CHAR(13) + CHAR(10) +
   CHAR(13) + CHAR(10)
DECLARE @text2 varchar(max) = 
   'Agent did not meet defined success criteria on this run.' + CHAR(13) + CHAR(10) +
   CHAR(13) + CHAR(10) +
   'Status: Completed' + CHAR(13) + CHAR(10) + 
   'Successful actions count: 106' + CHAR(13) + CHAR(10) + 
   'Page load count: 105' + CHAR(13) + CHAR(10) +
   CHAR(13) + CHAR(10) +
   CHAR(13) + CHAR(10)

CREATE TABLE Data (
   Id int,
   Mail_Body varchar(max) 
)
INSERT INTO Data 
   (Id, Mail_Body)
VALUES 
   (1, @text1),
   (2, @text2)

声明:

SELECT d.Id, j.[Status], j.Successful_actions_count, j.Page_load_count
FROM Data d
OUTER APPLY (
   SELECT
      MAX(CASE WHEN CHARINDEX('Status:', [value]) = 1 THEN REPLACE([value], 'Status:', '') END) AS [Status],
      MAX(CASE WHEN CHARINDEX('Successful actions count:', [value]) = 1 THEN REPLACE([value], 'Successful actions count:', '') END) AS [Successful_actions_count],
      MAX(CASE WHEN CHARINDEX('Page load count:', [value]) = 1 THEN REPLACE([value], 'Page load count:', '') END) AS [Page_load_count]
   FROM OPENJSON(CONCAT('["', REPLACE(d.Mail_Body, CHAR(13) + CHAR(10), '","'), '"]'))
) j

结果:

-----------------------------------------------------------
Id   Status      Successful_actions_count   Page_load_count
-----------------------------------------------------------
1    Completed   106                        105
2    Completed   106                        105