SQL：如何拆分含重复子串的回车分隔字符串为单行数据？

问题背景 #

我有如下以多空格分隔的数据：

'AM 1492       AM 1791       PM 1492       PM 1791       '

需要将每个数据项单独显示为一行。尝试过CharIndex定位字符、Substring截取，但不确定如何处理含重复子串（AM、PM）的字符串拆分，测试表及数据如下：

CREATE TABLE #T1
(
    StringInfo VARCHAR (500)
)

INSERT INTO #T1 (StringInfo)
VALUES ('AM 1492       AM 1791       PM 1492       PM 1791       ')

SELECT * 
FROM #T1 AS T

方法一：使用STRING_SPLIT（SQL Server 2016+） #

针对支持STRING_SPLIT的SQL Server版本，先将连续多空格替换为单空格，去除首尾空格后再拆分：

SELECT TRIM(value) AS Item
FROM #T1
CROSS APPLY STRING_SPLIT(TRIM(REPLACE(StringInfo, '  ', ' ')), ' ')
WHERE TRIM(value) <> '';

说明 #

1. REPLACE(StringInfo, ' ', ' ')：将字符串中连续的多空格替换为单个空格（若存在超过2个连续空格，可嵌套多次REPLACE，多数场景下两次替换足够）
2. TRIM()：去除字符串首尾的空格，避免拆分后出现空项
3. STRING_SPLIT(..., ' ')：按单个空格拆分字符串
4. 最后过滤掉空值，确保结果只保留有效数据项

方法二：递归CTE拆分（兼容旧版本SQL Server） #

若使用的SQL Server版本不支持STRING_SPLIT，可通过递归CTE逐个提取子串：

WITH SplitCTE AS (
    SELECT 
        1 AS RowNum,
        CHARINDEX(' ', StringInfo) AS SpacePos,
        TRIM(LEFT(StringInfo, CHARINDEX(' ', StringInfo))) AS Item,
        TRIM(SUBSTRING(StringInfo, CHARINDEX(' ', StringInfo) + 1, LEN(StringInfo))) AS RemainingString
    FROM #T1
    WHERE CHARINDEX(' ', StringInfo) > 0
    UNION ALL
    SELECT 
        RowNum + 1,
        CHARINDEX(' ', RemainingString),
        CASE 
            WHEN CHARINDEX(' ', RemainingString) > 0 THEN TRIM(LEFT(RemainingString, CHARINDEX(' ', RemainingString))) 
            ELSE TRIM(RemainingString) 
        END AS Item,
        CASE 
            WHEN CHARINDEX(' ', RemainingString) > 0 THEN TRIM(SUBSTRING(RemainingString, CHARINDEX(' ', RemainingString) + 1, LEN(RemainingString))) 
            ELSE '' 
        END AS RemainingString
    FROM SplitCTE
    WHERE RemainingString <> ''
)
SELECT Item
FROM SplitCTE
WHERE Item <> '';

说明 #

• 递归CTE的初始部分提取第一个数据项，并生成剩余未处理的字符串
• 递归部分持续从剩余字符串中提取下一个数据项，直到剩余字符串为空
• 最终过滤掉空项，得到每个单独的数据行

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