基于MS SQL从SWIFT消息字符串批量提取指定字段的技术咨询

在MS SQL环境下，要批量提取SwiftMessage.Body字段中所有成对的:20C::PREF//后的ID和:19A::AMCO//后的金额，可以用递归CTE遍历字符串，逐步定位并提取每组数据。以下是具体实现：

核心思路 #

1. 递归CTE从剩余字符串中定位第一个:20C::PREF//，提取对应ID；
2. 从该ID的结束位置开始，找到后续第一个:19A::AMCO//，提取对应金额；
3. 更新剩余字符串为当前:19A之后的内容，重复上述步骤，直到字符串中不再包含目标标签。

完整SQL代码 #

WITH RecursiveSwiftExtract AS (
    -- 初始锚点：筛选含目标标签的消息，初始化递归参数
    SELECT
        sm.Id AS MessageId,
        sm.Body AS OriginalBody,
        sm.Body AS RemainingBody,
        CHARINDEX(':20C::PREF//', sm.Body) AS PrefStartPos,
        1 AS GroupSeq
    FROM SwiftMessage sm
    WHERE sm.Body LIKE '%:20C::PREF//%'

    UNION ALL

    -- 递归提取：处理当前组，更新剩余字符串
    SELECT
        rse.MessageId,
        rse.OriginalBody,
        SUBSTRING(rse.RemainingBody, AmcoEndPos + 1, LEN(rse.RemainingBody)),
        CHARINDEX(':20C::PREF//', SUBSTRING(rse.RemainingBody, AmcoEndPos + 1, LEN(rse.RemainingBody))),
        rse.GroupSeq + 1
    FROM RecursiveSwiftExtract rse
    -- 计算:20C::PREF//后ID的起止位置
    CROSS APPLY (
        SELECT
            PrefStartPos + LEN(':20C::PREF//') AS PrefValueStart,
            COALESCE(
                NULLIF(CHARINDEX(CHAR(10), rse.RemainingBody, PrefStartPos), 0),
                NULLIF(CHARINDEX(':', rse.RemainingBody, PrefStartPos + LEN(':20C::PREF//')), 0),
                LEN(rse.RemainingBody) + 1
            ) AS PrefValueEnd
    ) PrefData
    -- 定位当前组对应的:19A::AMCO//位置
    CROSS APPLY (
        SELECT CHARINDEX(':19A::AMCO//', rse.RemainingBody, PrefData.PrefValueEnd) AS AmcoStartPos
    ) AmcoPos
    -- 计算:19A::AMCO//后金额的起止位置
    CROSS APPLY (
        SELECT
            AmcoPos.AmcoStartPos + LEN(':19A::AMCO//') AS AmcoValueStart,
            COALESCE(
                NULLIF(CHARINDEX(CHAR(10), rse.RemainingBody, AmcoPos.AmcoStartPos), 0),
                NULLIF(CHARINDEX(':', rse.RemainingBody, AmcoPos.AmcoStartPos + LEN(':19A::AMCO//')), 0),
                LEN(rse.RemainingBody) + 1
            ) AS AmcoValueEnd
    ) AmcoData
    -- 终止条件：剩余字符串中无目标标签
    WHERE rse.PrefStartPos > 0
)
-- 输出所有提取结果
SELECT
    MessageId,
    GroupSeq,
    SUBSTRING(RemainingBody, PrefData.PrefValueStart, PrefData.PrefValueEnd - PrefData.PrefValueStart) AS PrefId,
    SUBSTRING(RemainingBody, AmcoData.AmcoValueStart, AmcoData.AmcoValueEnd - AmcoData.AmcoValueStart) AS AmcoAmount
FROM RecursiveSwiftExtract
CROSS APPLY (
    SELECT
        PrefStartPos + LEN(':20C::PREF//') AS PrefValueStart,
        COALESCE(
            NULLIF(CHARINDEX(CHAR(10), RemainingBody, PrefStartPos), 0),
            NULLIF(CHARINDEX(':', RemainingBody, PrefStartPos + LEN(':20C::PREF//')), 0),
            LEN(RemainingBody) + 1
        ) AS PrefValueEnd
    ) PrefData
CROSS APPLY (
    SELECT CHARINDEX(':19A::AMCO//', RemainingBody, PrefData.PrefValueEnd) AS AmcoStartPos
) AmcoPos
CROSS APPLY (
    SELECT
        AmcoPos.AmcoStartPos + LEN(':19A::AMCO//') AS AmcoValueStart,
        COALESCE(
            NULLIF(CHARINDEX(CHAR(10), RemainingBody, AmcoPos.AmcoStartPos), 0),
            NULLIF(CHARINDEX(':', RemainingBody, AmcoPos.AmcoStartPos + LEN(':19A::AMCO//')), 0),
            LEN(RemainingBody) + 1
        ) AS AmcoValueEnd
) AmcoData
WHERE PrefStartPos > 0
ORDER BY MessageId, GroupSeq;

关键调整说明 #

• 标签分隔符适配：代码默认用换行符（CHAR(10)）或冒号作为标签边界，若你的SWIFT消息用\r\n分隔，需替换为CHAR(13)+CHAR(10)；
• 原始消息关联：用MessageId绑定原始记录，方便在SSRS报表中按消息分组展示数据；
• SSRS集成：将该查询作为报表数据集，直接拖入表格控件即可批量展示所有提取的ID和金额组。

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