SQL Server按文本拆分列：超额金额转多列需求及优化问询

针对你需要拆分SQL Server中存储的超额信息文本字段到指定三列的需求，结合SQL Server 2019标准版的特性，这里提供两种更简洁高效的替代写法，替代原有的CTE+CASE实现：

先准备测试数据 #

首先创建你提到的临时表并插入测试数据，方便验证效果：

CREATE TABLE #SourceData (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    ExcessText NVARCHAR(100)
);

INSERT INTO #SourceData (ExcessText)
VALUES
    ('£500 Per Year'),
    ('£200 Per Condition'),
    ('£300 Per Accident'),
    ('£100 Per Year, £400 Per Condition'),
    ('£150 Per Year, £250 Per Accident'),
    ('£600 Per Condition, £700 Per Accident'),
    ('£800 Per Year, £900 Per Condition, £1000 Per Accident');

写法一：直接行内提取+条件判断（适合简单场景，性能最优） #

这种写法无需CTE，直接通过字符串函数提取金额并按规则赋值，代码简洁且执行成本低：

SELECT
    Id,
    ExcessText,
    -- 仅当无Condition/Accident时才赋值Year
    ExcessPerYear = CASE
        WHEN ExcessText LIKE '%Per Condition%' OR ExcessText LIKE '%Per Accident%' THEN NULL
        WHEN ExcessText LIKE '%Per Year%' THEN TRY_CAST(
            SUBSTRING(ExcessText, PATINDEX('%[0-9]%', ExcessText), 
            PATINDEX('% Per Year%', ExcessText) - PATINDEX('%[0-9]%', ExcessText)) 
        AS DECIMAL(18,2))
        ELSE NULL
    END,
    -- 提取Accident对应的金额
    ExcessPerAccident = CASE
        WHEN ExcessText LIKE '%Per Accident%' THEN TRY_CAST(
            SUBSTRING(ExcessText, CHARINDEX('£', ExcessText, CHARINDEX('Per Accident', ExcessText) - 10) + 1, 
            PATINDEX('% Per Accident%', ExcessText) - CHARINDEX('£', ExcessText, CHARINDEX('Per Accident', ExcessText) - 10) - 1) 
        AS DECIMAL(18,2))
        ELSE NULL
    END,
    -- 提取Condition对应的金额
    ExcessPerCondition = CASE
        WHEN ExcessText LIKE '%Per Condition%' THEN TRY_CAST(
            SUBSTRING(ExcessText, CHARINDEX('£', ExcessText, CHARINDEX('Per Condition', ExcessText) - 10) + 1, 
            PATINDEX('% Per Condition%', ExcessText) - CHARINDEX('£', ExcessText, CHARINDEX('Per Condition', ExcessText) - 10) - 1) 
        AS DECIMAL(18,2))
        ELSE NULL
    END
FROM #SourceData;

写法二：拆分后PIVOT（适合复杂组合场景，扩展性强） #

如果你的文本字段存在更多组合情况，这种写法通过STRING_SPLIT拆分条目后，再按类型聚合赋值，新增类型时只需修改CASE判断，扩展性更好：

WITH SplitExcess AS (
    SELECT
        sd.Id,
        sd.ExcessText,
        -- 提取每个条目里的金额
        Amount = TRY_CAST(
            SUBSTRING(LTRIM(s.value), PATINDEX('%[0-9]%', LTRIM(s.value)), 
            PATINDEX('% Per %', LTRIM(s.value)) - PATINDEX('%[0-9]%', LTRIM(s.value))) 
        AS DECIMAL(18,2)),
        -- 标记每个条目对应的类型
        ExcessType = CASE
            WHEN LTRIM(s.value) LIKE '%Per Year%' THEN 'ExcessPerYear'
            WHEN LTRIM(s.value) LIKE '%Per Condition%' THEN 'ExcessPerCondition'
            WHEN LTRIM(s.value) LIKE '%Per Accident%' THEN 'ExcessPerAccident'
        END
    FROM #SourceData sd
    CROSS APPLY STRING_SPLIT(sd.ExcessText, ',') s
)
SELECT
    Id,
    ExcessText,
    -- 按规则优先：存在Condition/Accident时，Year字段置空
    ExcessPerYear = CASE 
        WHEN MAX(CASE WHEN ExcessType IN ('ExcessPerCondition', 'ExcessPerAccident') THEN 1 ELSE 0 END) = 0 
        THEN MAX(CASE WHEN ExcessType = 'ExcessPerYear' THEN Amount END) 
        ELSE NULL 
    END,
    ExcessPerAccident = MAX(CASE WHEN ExcessType = 'ExcessPerAccident' THEN Amount END),
    ExcessPerCondition = MAX(CASE WHEN ExcessType = 'ExcessPerCondition' THEN Amount END)
FROM SplitExcess
GROUP BY Id, ExcessText;

写法优势说明 #

1. 两种写法都使用TRY_CAST避免金额转换失败时抛出错误，增强鲁棒性；
2. 写法一无需额外的CTE或聚合操作，执行计划更简洁，适合数据量较大的场景；
3. 写法二通过拆分后聚合，逻辑更清晰，新增超额类型时只需修改CASE中的类型判断，维护成本更低。

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