坑人的SQL Server检测数字类型的函数ISNUMERIC

前几天，遇到一个十分棘手的问题，有同事提出在是字符类型的列中进行起止号计算，大体是这样的

新起号＝上一条止号+1

新止号＝新起号+数量

而在这一列的数据是这样的

库存

CN003?005007

000890670000

98111100 库存

370111900000

001,800,900,00

~~~~~~~~~~~~

木办法,问度娘吧,发现还是高人多啊,提出用ISNUMERIC(列名)=1,就是数字类型的,但是请看一下ISNUMERIC的说明文档

ISNUMERIC

 

语法   ISNUMERIC ( expression )
参数 expression  要计算的表达式。
返回类型 int 
备注 当输入表达式的计算结果为有效的 numeric 数据类型时，ISNUMERIC 返回 1；否则返回 0。有效的 numeric 数据类型包括以下类型：int,numeric,bigint,money,smallint,smallmoney,

tinyint,float,decimal,real 

 

注意：  对于不是数字的字符（如加号 (+)、减号 (-)）和有效货币符号（如美元符号 ($)）字符，ISNUMERIC 将返回 1。返回值为 1 确保可以将 expression 转换为上述数字类型中的一种。但是此函数存在Bug，当判断的表达式是字符类型的表达式时,就乱了!'001,800,900'同样会认为是数字类型

可以替代的写法是

PATINDEX('%[^0-9]%', 列名)

如果返回值等于0，则是纯数字型（没有0~9之外的字符）。

case when PATINDEX('%[^0-9]%', 列名)=0 then cast (列名 as decimal(18,0))+cast(1 as decimal(18,0)) else ~~~ end as 起号