T-SQL中提取的带逗号数字字符串无法转换为十进制数如何解决？

问题根源 #

• SQL Server 默认的数值格式中，小数分隔符为半角句号.，你提取出的字符串使用逗号,作为小数分隔符，不符合数值类型的格式规范，因此不管转int还是float都会触发类型转换失败的错误。
• 现有函数还存在潜在问题：如果原始字符串中存在多个逗号，提取结果会出现多逗号的非法格式，同样无法正常转换。

解决方法 #

快速适配方案（不用修改原函数） #

调用函数后先把字符串中的逗号替换为句号，再做类型转换即可正常运算，示例代码：

-- 转换为float做运算示例
SELECT CAST(REPLACE(dbo.udf_ExtractNumber(待处理字段), ',', '.') AS FLOAT) * 10 
FROM 对应表名

如果需要更高的数值精度，推荐转成decimal类型：

SELECT CAST(REPLACE(dbo.udf_ExtractNumber(待处理字段), ',', '.') AS DECIMAL(18,2)) 
FROM 对应表名

长期优化方案（修改自定义函数） #

直接在函数内部完成逗号转小数点的逻辑，同时增加多小数点校验，避免每次调用都手动处理，优化后的函数代码如下：

ALTER Function [dbo].[udf_ExtractNumber] (@String nvarchar(256))
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @AlphaNumeric varchar(256)
,@Res varchar(256)

SET @AlphaNumeric = @String
SET @Res = NULL

WHILE (PATINDEX('%[0-9.]%', @AlphaNumeric) > 0 )
BEGIN
    IF (PATINDEX('%[0-9.]%', @AlphaNumeric) >0 AND PATINDEX('%[0-9.]%', @AlphaNumeric) < CHARINDEX(',', @AlphaNumeric))
                BEGIN 
                    SET @Res = CONCAT(@Res ,SUBSTRING(@AlphaNumeric, PATINDEX('%[0-9.]%', @AlphaNumeric), 1) )
                    SET @AlphaNumeric = RIGHT(@AlphaNumeric,len(@AlphaNumeric)- PATINDEX('%[0-9.]%', @AlphaNumeric))
                END
            ELSE IF (CHARINDEX(',', @AlphaNumeric) >0  AND CHARINDEX(',', @AlphaNumeric) < PATINDEX('%[0-9.]%', @AlphaNumeric))
                BEGIN 
                    SET @Res = CONCAT(@Res , '.' )
                    SET @AlphaNumeric = RIGHT(@AlphaNumeric,len(@AlphaNumeric)- CHARINDEX(',', @AlphaNumeric))
                END
            ELSE IF (PATINDEX('%[0-9.]%', @AlphaNumeric) >0)
                BEGIN 
                    SET @Res = CONCAT(@Res, SUBSTRING(@AlphaNumeric, PATINDEX('%[0-9.]%', @AlphaNumeric), 1) )
                    SET @AlphaNumeric = RIGHT(@AlphaNumeric,len(@AlphaNumeric)- PATINDEX('%[0-9.]%', @AlphaNumeric))
                END
            ELSE IF (CHARINDEX(',', @AlphaNumeric) >0 )
                BEGIN 
                    SET @Res = CONCAT(@Res, '.' )
                    SET @AlphaNumeric = RIGHT(@AlphaNumeric,len(@AlphaNumeric)- CHARINDEX(',', @AlphaNumeric))
    END
END
-- 处理多个小数点的异常情况，仅保留第一个小数点
IF CHARINDEX('.', @Res) > 0
    SET @Res = STUFF(@Res, CHARINDEX('.', @Res)+1, LEN(@Res), REPLACE(SUBSTRING(@Res, CHARINDEX('.', @Res)+1, LEN(@Res)), '.', ''))
Return @Res
END

优化后函数的输出可以直接转换为数值类型使用。

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