如何在SQL Server中按逗号和分号拆分超长字符串为行列

问题背景 #

需将SQL Server表中NVARCHAR(MAX)类型的超长字符串拆分为独立行和列：

• 行以分号;分隔，列以逗号,分隔
• 字符串包含15列（含Tag编号、GPS坐标等字段），数据行数可达数千行
• 已尝试方法存在缺陷：
  • PARSENAME依赖点号，替换逗号为点号后因列数超过4限制产生大量NULL值
  • STRING_SPLIT仅能拆分出行，无法正确解析多列数据
  • 递归CTE仅生成单列结果，无法同时完成行列拆分

数据示例 #

"Tag No,Tag2 No,Location ID,Log Length,Log Diameter,Log Thick Diameter,Volume,Team ID,Scan DateTime,Checker ID,LogType ID,Latitude,Longitude,Altitude,HCS No;67599715,0,155341,330,17,0,0.0901491,144,2022-11-16 13:59:12.208,1008,HEWSAW,0,0,0,83493;67599716,0,155341,330,15,0,0.0718509,144,2022-11-16 13:59:16.997,1008,HEWSAW,0,0,0,83493;..."

可行解决方案 #

方法1：STRING_SPLIT + OPENJSON（SQL Server 2016+） #

先通过STRING_SPLIT拆分每行数据，再用OPENJSON将每行的逗号分隔字符串转换为JSON数组，提取对应列值。该方法性能优于递归CTE，适合高版本SQL Server。

代码示例

-- 从源表获取目标字符串
DECLARE @LogString NVARCHAR(MAX) = (SELECT LogString FROM syncbuffer WHERE id = '522900');

WITH SplitRows AS (
    -- 拆分出行并过滤空行（避免末尾分号产生无效值）
    SELECT TRIM(value) AS RowData
    FROM STRING_SPLIT(@LogString, ';')
    WHERE TRIM(value) <> ''
),
ParsedColumns AS (
    SELECT
        -- 按数组索引提取对应列值
        JSON_VALUE(jsonData, '$[0]') AS [Tag No],
        JSON_VALUE(jsonData, '$[1]') AS [Tag2 No],
        JSON_VALUE(jsonData, '$[2]') AS [Location ID],
        JSON_VALUE(jsonData, '$[3]') AS [Log Length],
        JSON_VALUE(jsonData, '$[4]') AS [Log Diameter],
        JSON_VALUE(jsonData, '$[5]') AS [Log Thick Diameter],
        JSON_VALUE(jsonData, '$[6]') AS [Volume],
        JSON_VALUE(jsonData, '$[7]') AS [Team ID],
        JSON_VALUE(jsonData, '$[8]') AS [Scan DateTime],
        JSON_VALUE(jsonData, '$[9]') AS [Checker ID],
        JSON_VALUE(jsonData, '$[10]') AS [LogType ID],
        JSON_VALUE(jsonData, '$[11]') AS [Latitude],
        JSON_VALUE(jsonData, '$[12]') AS [Longitude],
        JSON_VALUE(jsonData, '$[13]') AS [Altitude],
        JSON_VALUE(jsonData, '$[14]') AS [HCS No]
    FROM SplitRows
    -- 将逗号分隔字符串转为JSON数组格式
    CROSS APPLY (SELECT CONCAT('["', REPLACE(RowData, ',', '","'), '"]') AS jsonData) AS JsonConvert
)
SELECT * FROM ParsedColumns;

方法2：递归CTE拆分行列（兼容低版本SQL Server） #

针对SQL Server 2016以下版本，通过两次递归CTE分别拆分行和列，最后将列数据行转列得到结果。

代码示例

DECLARE @LogString NVARCHAR(MAX) = (SELECT LogString FROM syncbuffer WHERE id = '522900');

-- 第一步：拆分出行
WITH SplitRows AS (
    SELECT 
        1 AS RowNum,
        LEFT(@LogString, CHARINDEX(';', @LogString) - 1) AS RowData,
        STUFF(@LogString, 1, CHARINDEX(';', @LogString), '') AS RemainingString
    WHERE CHARINDEX(';', @LogString) > 0
    UNION ALL
    SELECT 
        RowNum + 1,
        LEFT(RemainingString, CHARINDEX(';', RemainingString + ';') - 1),
        STUFF(RemainingString, 1, CHARINDEX(';', RemainingString + ';'), '')
    FROM SplitRows
    WHERE RemainingString <> ''
),
-- 第二步：拆分每行的列
SplitColumns AS (
    SELECT
        RowNum,
        1 AS ColNum,
        LEFT(RowData, CHARINDEX(',', RowData + ',') - 1) AS ColValue,
        STUFF(RowData, 1, CHARINDEX(',', RowData + ','), '') AS RemainingCols
    FROM SplitRows
    WHERE TRIM(RowData) <> ''
    UNION ALL
    SELECT
        RowNum,
        ColNum + 1,
        LEFT(RemainingCols, CHARINDEX(',', RemainingCols + ',') - 1),
        STUFF(RemainingCols, 1, CHARINDEX(',', RemainingCols + ','), '')
    FROM SplitColumns
    WHERE RemainingCols <> ''
)
-- 行转列得到最终结构
SELECT
    MAX(CASE WHEN ColNum = 1 THEN ColValue END) AS [Tag No],
    MAX(CASE WHEN ColNum = 2 THEN ColValue END) AS [Tag2 No],
    MAX(CASE WHEN ColNum = 3 THEN ColValue END) AS [Location ID],
    MAX(CASE WHEN ColNum = 4 THEN ColValue END) AS [Log Length],
    MAX(CASE WHEN ColNum = 5 THEN ColValue END) AS [Log Diameter],
    MAX(CASE WHEN ColNum = 6 THEN ColValue END) AS [Log Thick Diameter],
    MAX(CASE WHEN ColNum = 7 THEN ColValue END) AS [Volume],
    MAX(CASE WHEN ColNum = 8 THEN ColValue END) AS [Team ID],
    MAX(CASE WHEN ColNum = 9 THEN ColValue END) AS [Scan DateTime],
    MAX(CASE WHEN ColNum = 10 THEN ColValue END) AS [Checker ID],
    MAX(CASE WHEN ColNum = 11 THEN ColValue END) AS [LogType ID],
    MAX(CASE WHEN ColNum = 12 THEN ColValue END) AS [Latitude],
    MAX(CASE WHEN ColNum = 13 THEN ColValue END) AS [Longitude],
    MAX(CASE WHEN ColNum = 14 THEN ColValue END) AS [Altitude],
    MAX(CASE WHEN ColNum = 15 THEN ColValue END) AS [HCS No]
FROM SplitColumns
GROUP BY RowNum
ORDER BY RowNum
OPTION (MAXRECURSION 0); -- 处理超过100行时启用，解除递归次数限制

注意事项 #

• 若字符串包含双引号、逗号等特殊字符，需提前转义避免JSON解析错误
• 递归CTE处理大量数据时需启用MAXRECURSION 0，默认递归限制为100

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