SQL Server 2016中Substring多结果报错及多段数字提取优化咨询

一、修复"多结果"报错问题 #

你的报错根源在于未关联的子查询：当你在主查询里嵌套(Select substring(failover, 10, 5) ...)时，这个子查询会返回所有符合Failover like '$D$Failov:%'的行的结果，而主查询的每一行都试图接收这个多行结果，自然会触发"Subquery returned more than 1 value"错误。

最简单的修复方式是去掉嵌套子查询，直接把substring和Account放在同一个SELECT列表里：

SELECT 
    account,
    SUBSTRING(failover, 10, 5) AS "1.Result"
FROM dbo.f009 
WHERE Failover LIKE '$D$Failov:%'

如果一定要用子查询（比如复杂场景下），必须添加行级关联条件，确保子查询只返回当前行的结果：

SELECT 
    account,
    (SELECT SUBSTRING(f2.failover, 10, 5) 
     FROM dbo.f009 f2 
     WHERE f2.Failover LIKE '$D$Failov:%'
       AND f2.account = f1.account) AS "1.Result" -- 假设account是唯一标识，或用主键关联
FROM dbo.f009 f1 
WHERE f1.Failover LIKE '$D$Failov:%'

二、简洁提取第二、第三个数字段 #

由于SQL Server 2016的STRING_SPLIT不支持返回拆分后的顺序（ordinal列在2022版本才引入），我们可以用以下几种简洁方法：

方法1：多次使用CHARINDEX定位分隔符 #

通过依次找到每个冒号的位置，精准提取对应段：

SELECT 
    account,
    -- 第一个数字段：从第10位开始，到第一个冒号结束
    SUBSTRING(failover, 10, CHARINDEX(':', failover, 10) - 10) AS "1.Result",
    -- 第二个数字段：第一个冒号后到第二个冒号结束
    SUBSTRING(failover, CHARINDEX(':', failover, 10) + 1, 
              ISNULL(NULLIF(CHARINDEX(':', failover, CHARINDEX(':', failover, 10)+1), 0), LEN(failover)+1) - CHARINDEX(':', failover, 10) -1) AS "2.Result",
    -- 第三个数字段：第二个冒号后到字符串结束
    CASE WHEN CHARINDEX(':', failover, CHARINDEX(':', failover, 10)+1) > 0 
         THEN SUBSTRING(failover, CHARINDEX(':', failover, CHARINDEX(':', failover, 10)+1)+1, LEN(failover)) 
         ELSE '' END AS "3.Result"
FROM dbo.f009 
WHERE Failover LIKE '$D$Failov:%'

这里用ISNULL(NULLIF(...))处理某些行没有第二/第三个冒号的情况（比如$D$Failov:12345:这种），避免提取空值或报错。

方法2：利用XML拆分字符串 #

把字符串转换成XML格式，通过节点索引提取对应段，可读性更好：

WITH SplitCTE AS (
    SELECT 
        account,
        -- 替换前缀和分隔符，生成XML
        CAST('<v>' + REPLACE(REPLACE(failover, '$D$Failov:', ''), ':', '</v><v>') + '</v>' AS XML) AS XmlValues
    FROM dbo.f009 
    WHERE Failover LIKE '$D$Failov:%'
)
SELECT 
    account,
    XmlValues.value('/v[1]', 'VARCHAR(20)') AS "1.Result",
    XmlValues.value('/v[2]', 'VARCHAR(20)') AS "2.Result",
    XmlValues.value('/v[3]', 'VARCHAR(20)') AS "3.Result"
FROM SplitCTE

这个方法自动处理空段（比如$D$Failov:12345:的第二个段会返回空字符串），代码更简洁易维护。

方法3：递归CTE拆分（适合复杂多段场景） #

如果后续可能有更多段需要提取，递归CTE是更通用的方案：

WITH RecursiveSplit AS (
    SELECT 
        account,
        failover,
        -- 去掉前缀后的剩余字符串
        STUFF(failover, 1, 9, '') AS RemainingStr,
        1 AS SegmentNumber,
        -- 第一个段
        SUBSTRING(STUFF(failover, 1, 9, ''), 1, ISNULL(NULLIF(CHARINDEX(':', STUFF(failover, 1, 9, '')), 0), LEN(STUFF(failover, 1, 9, ''))+1)-1) AS SegmentValue
    FROM dbo.f009 
    WHERE Failover LIKE '$D$Failov:%'
    
    UNION ALL
    
    SELECT 
        account,
        failover,
        STUFF(RemainingStr, 1, ISNULL(NULLIF(CHARINDEX(':', RemainingStr), 0), LEN(RemainingStr)+1), '') AS RemainingStr,
        SegmentNumber + 1 AS SegmentNumber,
        SUBSTRING(RemainingStr, 1, ISNULL(NULLIF(CHARINDEX(':', RemainingStr), 0), LEN(RemainingStr)+1)-1) AS SegmentValue
    FROM RecursiveSplit
    WHERE LEN(RemainingStr) > 0
)
-- 用PIVOT转成列格式
SELECT 
    account,
    [1] AS "1.Result",
    [2] AS "2.Result",
    [3] AS "3.Result"
FROM RecursiveSplit
PIVOT (
    MAX(SegmentValue) FOR SegmentNumber IN ([1], [2], [3])
) AS PivotTable

这个方法可以轻松扩展到提取更多段，只需修改PIVOT中的列即可。

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