SQL Server 2008 R2 的 Try_Convert

2022-01-03 00:00:00 sql sql-server-2008 sql-server sql-server-2012

我正在使用 SQL Server 2008 R2 并且有一个 VARCHAR 列我想使用 CONVERT 转换为 DECIMAL(28,10).但是其中许多行的格式不正确，因此无法将它们解析为数字.在这种情况下，我只想通过将结果设置为 0 或 NULL 来跳过那些.

I'm using SQL Server 2008 R2 and have a VARCHAR column I want to convert to DECIMAL(28,10) using CONVERT. But many of those rows are badly formatted, so it is not possible to parse them to a number. In that case I just want to skip those by setting result to 0 or NULL.

我知道 SQL Server 2012 中有一个新语句 (TRY_CONVERT()) 会很方便.

I know there is a new statement in SQL Server 2012 (TRY_CONVERT()) that would be handy.

这在 2008 年是否可行，还是必须等到我们更新到 SQL 2012 的下一个版本?

Is this possible in 2008 or must I wait until we update to next version SQL 2012?

编辑

不幸的是，ISNUMERIC() 在这种情况下是不可靠的.我试过

Unfortunately ISNUMERIC() is not reliable in this case. I tried 

ISNUMERIC(myCol) = 1

对于 CONVERT 无法转换为 DECIMAL 的行，它返回 true.

That returns true for rows that CONVERT is not able to convert to DECIMAL.

推荐答案

终于在 SO 和 Google 的帮助下找到了如何制作它.

Finally found out how to make it with the help from SO and Google.

更新声明:

UPDATE PriceTerm 
SET PercentAddition = CONVERT(decimal(28,10), RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(AdditionalDescription,'%',''), ',','.'), '&', '')))) 
WHERE AdditionalDescription LIKE '%[%]%' AND
dbo.isreallynumeric(RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(AdditionalDescription,'%',''), ',','.'), '&', '')))) = 1 AND
PercentAddition = 0

首先，我搜索 % char 作为大多数时间用作百分比值的标记.但也有随机的其他用途.结果证明 ISNUMERIC 在我的情况下不可靠.

First I search for % char as most of the times that is used as a marker for the percentvalue. But there is also random other uses. It turned out that ISNUMERIC was not reliable in my case.

真正不同的是对存储过程的调用是来自 这里.

What really make difference is the call to stored procedure isreallynumeric from here.

所以

CREATE FUNCTION dbo.isReallyNumeric  
(  
    @num VARCHAR(64)  
)  
RETURNS BIT  
BEGIN  
    IF LEFT(@num, 1) = '-'  
        SET @num = SUBSTRING(@num, 2, LEN(@num))  

    DECLARE @pos TINYINT  

    SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))  

    RETURN CASE  
    WHEN PATINDEX('%[^0-9.-]%', @num) = 0  
        AND @num NOT IN ('.', '-', '+', '^') 
        AND LEN(@num)>0  
        AND @num NOT LIKE '%-%' 
        AND  
        (  
            ((@pos = LEN(@num)+1)  
            OR @pos = CHARINDEX('.', @num))  
        )  
    THEN  
        1  
    ELSE  
    0  
    END  
END  
GO

相关文章