无法将字符串转换为十进制数字- T-sql

Question

我使用这个函数从字符串中提取十进制数:

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 ,SUBSTRING(@AlphaNumeric, CHARINDEX(',', @AlphaNumeric), 1) )
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,SUBSTRING(@AlphaNumeric, CHARINDEX(',', @AlphaNumeric), 1))
SET @AlphaNumeric = RIGHT(@AlphaNumeric,len(@AlphaNumeric)- CHARINDEX(',', @AlphaNumeric))
END
END
Return @Res

这个工作很好，输出正是我想要的。例如，它转换为" adfsadf18,12 adfssfa"18日,12但是，当我想要处理数字时，我无法将其转换为浮点数。除以或乘以它会产生以下错误:

将nvarchar值'7,5'转换为int数据类型时，转换失败。

Answer 1

根据Ali Fidenli的注释，我调整了代码的最后一部分以使其正常工作。由于

END
Return replace(@Res,',','.')