如何在SQL中拆分以HH:MM:SS:为分隔符的通话日志字符串？

我有一个存储通话事件日志的表calldetail，其中CallEventLog字段的格式如下：

HH:MM:SS: Call Initiated HH:MM:SS: Call answered HH:MM:SS: Menu Main HH:MM:SS: Menu Self Service HH:MM:SS: Member Premium HH:MM:SS: One Time Payment HH:MM:SS: Transfer to agent HH:MM:SS: Disconnected [Remote Disconnect]

需要将该字段拆分为每行对应一个日志段的格式，单条日志期望格式：

HH:MM:SS: Call Initiated
HH:MM:SS: Call answered
HH:MM:SS: Menu Main
HH:MM:SS: Menu Self Service
HH:MM:SS: Member Premium
HH:MM:SS: One Time Payment
HH:MM:SS: Transfer to agent
HH:MM:SS: Disconnected [Remote Disconnect]

尝试过的方法及问题 #

我尝试用STRING_SPLIT结合多字符分隔符替换的方式实现，但结果不符合预期：

DECLARE @OLDdelim varchar(2)    = ': '
DECLARE @NEWdelim nchar(1)  = NCHAR(9999)

SELECT CallId,CallDate,value FROM calldetail cd
CROSS APPLY STRING_SPLIT(REPLACE(cd.CallEventLog, @OLDdelim, @NEWdelim), @NEWdelim)

错误结果示例：

CallId                  CallDate    value
100264416140230613      2023-06-13  06:07:53
100264416140230613      2023-06-13  Initializing 06:07:55
100264416140230613      2023-06-13  Offering 06:07:57
100264416140230613      2023-06-13  Call answered 06:08:14
100264416140230613      2023-06-13  Menu Main 06:08:17
100264416140230613      2023-06-13  Menu Self Service 06:08:21

期望最终结果 #

CallId                  CallDate        LogEventTime_Description
100264416140230613      2023-06-13      06:07:53 Initializing
100264416140230613      2023-06-13      06:07:55 Offering
100264416140230613      2023-06-13      06:07:57 Call answered
100264416140230613      2023-06-13      06:08:14 Main Menu
100264416140230613      2023-06-13      06:08:17 Menu Self Service
100264416140230613      2023-06-13      06:08:21 Member Premium

正确的SQL实现方案 #

方案1：SQL Server 2022+专属方案（利用STRING_SPLIT序号参数）

如果使用SQL Server 2022及以上版本，可借助STRING_SPLIT的enable_ordinal参数保留拆分顺序，再通过窗口函数将时间与描述配对：

SELECT 
    CallId,
    CallDate,
    CONCAT(LAG(value, 1) OVER (PARTITION BY CallId ORDER BY ordinal), ' ', value) AS LogEventTime_Description
FROM calldetail cd
CROSS APPLY STRING_SPLIT(REPLACE(cd.CallEventLog, ': ', '|'), '|', 1)
WHERE ordinal % 2 = 0 -- 仅取描述行，与前一行的时间字段配对

方案2：兼容SQL Server 2016-2019版本的XML拆分法

针对低版本SQL Server，用XML处理多字符分隔符的拆分，确保顺序正确：

SELECT 
    cd.CallId,
    cd.CallDate,
    LTRIM(SUBSTRING(split.value, 1, CHARINDEX(':', split.value, 4) - 1)) + ' ' + 
    SUBSTRING(split.value, CHARINDEX(':', split.value, 4) + 2, LEN(split.value)) AS LogEventTime_Description
FROM calldetail cd
CROSS APPLY (
    SELECT CAST('<x>' + REPLACE(cd.CallEventLog, ' HH:', '</x><x>HH:') + '</x>' AS XML) AS xml_data
) x
CROSS APPLY x.xml_data.nodes('x') AS n(split)

方案3：正则分组拆分（适合复杂描述场景）

如果日志描述包含空格，可通过分组逻辑拆分，需SQL Server 2017+支持STRING_AGG：

SELECT 
    CallId,
    CallDate,
    TRIM(STRING_AGG(s.value, ' ') WITHIN GROUP (ORDER BY s.ordinal)) AS LogEventTime_Description
FROM calldetail cd
CROSS APPLY STRING_SPLIT(cd.CallEventLog, ' ', 1) s
CROSS APPLY (
    SELECT FLOOR((s.ordinal - 1) / (CHARINDEX(':', s.value) > 0 AND s.value LIKE '[0-9][0-9]:%' THEN 4 ELSE 1 END)) AS group_id
) g
GROUP BY CallId, CallDate, group_id

内容的提问来源于stack exchange，提问作者Blake Wilson