MSSQL 2012如何将带+/-后缀的字符串转换为float类型数值

核心思路 #

需求本质是排除字符串开头的正负号后，定位第一个出现的+/-作为分割点，截取分割点前的子串再转换为float类型。直接对原始字符串用TRY_CONVERT会因为字符串包含运算符后缀，无法识别为合法数值返回NULL。

实现代码 #

MSSQL 2012 原生支持PATINDEX、STUFF、SUBSTRING、TRY_CONVERT函数，不需要依赖高版本特性，代码如下：

SELECT
    TRY_CONVERT(
        FLOAT,
        SUBSTRING(
            col,
            1,
            -- 定位分割点位置，兼容无后缀运算符的纯数值场景
            CASE
                WHEN PATINDEX('%[+-]%', STUFF(col, 1, 1, '')) = 0 THEN LEN(col)
                ELSE PATINDEX('%[+-]%', STUFF(col, 1, 1, ''))
            END
        )
    ) AS col
FROM tb

逻辑说明 #

• STUFF(col,1,1,'')：删除字符串首字符，排除数值本身的开头正负号对分割点匹配的干扰
• PATINDEX('%[+-]%', 处理后字符串)：匹配删除首字符后第一个出现的+/-位置，对应原字符串中作为运算符的分割点前的截取长度
• 当匹配不到分割点（即字符串本身是纯数值无后缀运算表达式）时，直接取整个字符串长度做截取，避免截断异常
• 最后用TRY_CONVERT转float，遇到非法格式值会返回NULL不会抛错

样例验证结果 #

针对给出的测试数据，执行后返回结果完全匹配预期：

内容的提问来源于stack exchange，提问作者Tae In Kim