SQL中非标准储蓄字符串字段转换为千单位整数的解决方案咨询

修改后完整查询（T-SQL 环境） #

SELECT 
    [Savings],
    CAST(
        CASE
            -- 处理NULL、含days的场景直接返回0
            WHEN [Savings] IS NULL OR [Savings] LIKE '%days%' THEN 0
            ELSE
                ROUND(
                    -- 转换提取到的数值为浮点型用于后续计算
                    CAST(
                        LEFT(SUBSTRING([Savings], PATINDEX('%[0-9.-]%', [Savings]), 8000),
                            PATINDEX('%[^0-9.-]%', SUBSTRING([Savings], PATINDEX('%[0-9.-]%', [Savings]), 8000) + 'X') -1
                        ) AS FLOAT
                    )
                    -- 按单位做倍率转换
                    * CASE
                        WHEN [Savings] LIKE '%M%' THEN 1000
                        WHEN [Savings] LIKE '%K%' OR [Savings] LIKE '%thousand%' THEN 1
                        ELSE 1
                      END
                , 0)
        END
    AS INT) AS [Savings (K)]
FROM tbl

逻辑说明 #

1. 新增第一层CASE判断，命中NULL、包含days的字符串直接返回0，解决原有查询无法处理带days值的问题
2. 保留原有数值提取逻辑，将提取结果转为浮点型后调用ROUND函数四舍五入到整数位，解决浮点值无法正确取整的问题
3. 新增单位转换逻辑，兼容M（1M对应1000K）、K/thousand（对应1K）的特殊格式值转换，完全匹配示例要求的输出结果

转换效果验证 #

和要求的预期输出完全匹配：

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