SQL Server 2005如何批量转换分号分隔的多日期字符串格式

针对SQL Server 2005中拆分分号分隔的多日期字符串并转换为DD/MM/YYYY格式的需求，可以通过递归CTE拆分字符串 + FOR XML PATH拼接结果的方式实现，具体方案如下：

实现代码 #

WITH numbered_dates AS (
    -- 为每行生成唯一标识，若表已有主键（如ID）可直接替换row_id
    SELECT 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_id,
        SENTDATE
    FROM dates
),
split_dates AS (
    -- 递归CTE初始化：将原始字符串作为初始待拆分内容
    SELECT 
        row_id,
        CAST('' AS VARCHAR(MAX)) AS remainder,
        CAST(SENTDATE AS VARCHAR(MAX)) AS current_str,
        CAST('' AS VARCHAR(MAX)) AS date_part
    FROM numbered_dates
    UNION ALL
    -- 递归拆分每个分号分隔的日期片段
    SELECT 
        row_id,
        CASE 
            WHEN CHARINDEX(';', current_str) > 0 THEN SUBSTRING(current_str, CHARINDEX(';', current_str) + 1, LEN(current_str))
            ELSE ''
        END AS remainder,
        CASE 
            WHEN CHARINDEX(';', current_str) > 0 THEN SUBSTRING(current_str, 1, CHARINDEX(';', current_str) - 1)
            ELSE current_str
        END AS current_str,
        -- 仅提取长度为8的有效日期片段（YYYYMMDD格式）
        CASE 
            WHEN CHARINDEX(';', current_str) > 0 AND LEN(SUBSTRING(current_str, 1, CHARINDEX(';', current_str) - 1)) = 8 
                THEN SUBSTRING(current_str, 1, CHARINDEX(';', current_str) - 1)
            WHEN CHARINDEX(';', current_str) = 0 AND LEN(current_str) = 8 
                THEN current_str
            ELSE ''
        END AS date_part
    FROM split_dates
    WHERE current_str <> ''
)
-- 拼接转换后的日期，还原分号分隔格式
SELECT 
    STUFF((
        SELECT ';' + SUBSTRING(date_part, 7, 2) + '/' + SUBSTRING(date_part, 5, 2) + '/' + SUBSTRING(date_part, 1, 4)
        FROM split_dates sd
        WHERE sd.row_id = nd.row_id AND sd.date_part <> ''
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS formatted_sentdate
FROM numbered_dates nd
GROUP BY nd.row_id, nd.SENTDATE
ORDER BY nd.row_id
-- 若单行列中日期数量超过100，需添加以下选项取消递归次数限制
-- OPTION (MAXRECURSION 0);

方案说明 #

1. 编号行数据：通过ROW_NUMBER()为每行生成唯一标识row_id，确保拆分后的日期能对应回原始行；若表已有主键，可直接使用主键替代。
2. 递归拆分字符串：利用递归CTE逐个拆分分号分隔的日期片段，同时过滤掉非8位的无效日期内容。
3. 日期格式转换：对每个有效日期片段使用SUBSTRING截取年、月、日部分，重组为DD/MM/YYYY格式。
4. 拼接结果：通过FOR XML PATH('')将同一行的转换后日期拼接为字符串，并用STUFF移除开头多余的分号。

注意事项 #

• 若单行列中日期数量超过100，需在查询末尾添加OPTION (MAXRECURSION 0)取消递归次数限制。
• 确保SENTDATE列中所有有效日期均为YYYYMMDD格式，否则转换结果会出错。

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