T-SQL ISNUMERIC('45D-1')返回1 [英] T-Sql ISNUMERIC('45D-1') returns 1

问题描述

我有一条T-Sql语句，该语句正在从表中获取下一个可用数字. 但该列的类型为nvarchar. 它确实必须是一个数字，但是我仍然需要能够获得下一个可用的数字

I have a T-Sql statement that is getting the next available number from a table. but the column is of type nvarchar. It dost have to be a number, But i still need to be able to get the next available number

因此，当我运行sql时，我尝试仅获取数字

so when i run the sql i try to only get numbers

WHERE
ISNUMERIC(myCol) AND CAST(REPLACE(myCol, N'0', N'') AS int)

但是我得到了错误

Conversion failed when converting the nvarchar value '45D-1' to data type int.

所以我只跑了

select ISNUMERIC('45D-1')

令我惊讶的是它的真实性

And to my surprise its true

我已经写了一个"代替.这是有道理的，但是为什么"D"是一个数字?以及为什么不能sql强制转换呢?并有一个函数可以真正测试其是否为Int吗?

I already wrote a replace for "." which makes sense, But why is "D" a number? And why cant sql cast it? And is there a function to really test if its a Int?

推荐答案

INT和NUMERIC是不同的. ISNUMERIC返回的任何内容可能可能是数字类型(INT, BIGINT, DECIMAL, FLOAT, MONEY).

INT and NUMERIC isn't the same. ISNUMERIC is returning anything that could possibly be a numeric type (INT, BIGINT, DECIMAL, FLOAT, MONEY).

示例:SELECT ISNUMERIC('$')

这不是INT，但返回true，因为它是正确格式化为MONEY的格式. ISNUMERIC也适用于科学记数法....SELECT ISNUMERIC('1e4')将变为真.我要说的是在不了解ISNUMERIC所评估的内容或所有可能性的情况下就不会信任它.

This isn't an INT but returns true because it is something that is correctly formatted as MONEY. ISNUMERIC also works on scientific notations.... SELECT ISNUMERIC('1e4') would result to true. The point i'm making is don't trust ISNUMERIC out right without understanding what it is evaluating, or all the possibilities.

这篇关于T-SQL ISNUMERIC('45D-1')返回1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！