如何将含其他字符的VARCHAR转换为decimal/smallmoney类型

问题场景 #

我尝试创建一个函数，从输入内容中提取有效数值并返回smallmoney类型。当输入值为'kjkh 1.99 hkhj'时，触发错误：

转换smallmoney值到varchar时结果空间不足

将函数返回类型改为RETURNS SMALLMONEY后，又出现新错误：

无法将char值转换为smallmoney，char值语法不正确

原函数代码 #

CREATE OR ALTER FUNCTION dbo.StripNonNumerics
(
  @Temp varchar(40)
)
RETURNS VARCHAR
AS
BEGIN

    DECLARE @KeepValues AS VARCHAR(40)
    SET @KeepValues = '%[^0-9].%'
    WHILE PATINDEX(@KeepValues, @Temp) > 0
        SET @Temp = STUFF(@Temp, PATINDEX(@KeepValues, @Temp), 1, '')
    
    RETURN (SELECT CAST(TRIM(@Temp) AS SMALLMONEY));
END;

错误原因与解决方法 #

原函数中的正则匹配模式%[^0-9].%逻辑错误：该模式会匹配非数字字符且紧跟一个小数点的内容，而非保留数字和小数点。这导致提取后的字符串无法形成有效的数值格式，进而触发转换错误。

将匹配模式修改为%[^0-9.]%即可解决问题——这个模式会匹配所有0-9和.之外的字符，从而正确提取出带小数点的有效数值字符串。

修改后的关键代码行：

SET @KeepValues = '%[^0-9.]%'

修改后，函数能正确提取出1.99，无论是转换为smallmoney还是设置函数返回类型为SMALLMONEY，都能正常运行。

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