SQL Server不靠谱的isnumeric函数【转】

MS SQL Server 的 isnumeric 函数返回为1的字符串有时候并不能被直接 CAST 成数值型字段。比如包含逗号(12,345)、制表符的字符串用isnumeric来判断的话返回都是1。所以在进行某些数据转化的时候想靠 isnumeric 来保证转化脚本不报数据类型转换失败的错误是不保险的。

解决办法只有用额外的字符串校验来做判断:

IF  OBJECT_ID(N ' dbo.isReallyNumeric ', N ' FN 'IS  NOT  NULL
DROP  FUNCTION dbo.isReallyNumeric;
GO
CREATE 
FUNCTION [dbo].[isReallyNumeric]
(
    @num VARCHAR(128)
)
RETURNS BIT
BEGIN
    set @num = LTRIM(RTRIM(@num))
    IF LEFT(@num, 1) = '-'
        SET @num = SUBSTRING(@num, 2, LEN(@num))
    DECLARE @pos INTEGER
    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))
        )
        AND Isnumeric(@num)>0 --这里再次判断一下。因为如果符号为ⅠⅡ这样的特殊字符就不行。
    THEN
        1
    ELSE
    0
    END
END


GO
IF OBJECT_ID(N'dbo.isReallyInteger', N'FN'IS NOT NULL
DROP FUNCTION dbo.isReallyInteger;
GO
CREATE FUNCTION dbo.isReallyInteger
(
    @num VARCHAR(128)
)
RETURNS BIT
BEGIN
    set @num = LTRIM(RTRIM(@num))
    IF LEFT(@num1= '-'
        SET @num = SUBSTRING(@num2LEN(@num))
    RETURN CASE
    WHEN PATINDEX('%[^0-9-]%'@num= 0
        AND CHARINDEX('-'@num<= 1
        AND @num NOT IN ('.''-''+''^')
        AND LEN(@num)>0
        AND @num NOT LIKE '%-%'
        AND Isnumeric(@num)>0 --这里再次判断一下。因为如果符号为ⅠⅡ这样的特殊字符就不行。    

    THEN
        1
    ELSE
        0
    END
END
GO

 

通过创建以上的自定义函数来检查字符串是否确实可以被CAST到数值型字段来保存,替换掉调用 isnumeric 的地方即可(因为带前后导空格的字符串可以被自动CAST,所以先做TRIM处理)。

转载于:https://www.cnblogs.com/kokyu/archive/2012/07/23/2604492.html

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值