ISNUMERIC('07213E71') = True?

问题:

SQL is detecting that the following string ISNUMERIC:

'07213E71'

I believe this is because the 'E' is being classed as a mathmatical symbol.

However, I need to ensure that only values which are whole integers are returned as True.

How can I do this?

回答1:

07213E71 is a floating number 7213 with 71 zeros

You can use this ISNUMERIC(myValue + '.0e0') to test for whole integers. Slightly cryptic but works.

Another test is the double negative myValue NOT LIKE '%[^0-9]%' which allows only digits 0 to 9.

ISNUMERIC has other issues in that these all return 1: +, -,

回答2:

To nitpick: This is a whole integer. It is equivalent to 7213 * 10 ^ 71.

回答3:

In the documentation it says

ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0. A return value of 1 guarantees that expression can be converted to one of these numeric types.

Your number is also float (with exponential notation), therefore the only way to have ISINTEGER is to define it yourself on SQL. Read the following link.

http://classicasp.aspfaq.com/general/what-is-wrong-with-isnumeric.html

Extras:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59049

http://www.tek-tips.com/faqs.cfm?fid=6423

回答4:

I have encountered the same problem. IsNumeric accepts "$, €, +, -, etc" as valid inputs and Convert function throws errors because of this. Using "LIKE" SQL statement fixed my problem. I hope it'll help the others

SELECT UnitCode, UnitGUID, Convert(int, UnitCode) AS IntUnitCode
      FROM [NG_Data].[NG].[T_GLB_Unit]  
     WHERE ISNULL(UnitType,'') <>'Department'
       AND UnitCode NOT LIKE '%[^0-9]%'
  ORDER BY IntUnitCode

PS: don't blame me for using "UnitCode" as nvarchar :) It is an old project :)

回答5:

You have to ensure it out of the call to the database, whatever the language you work with, and then pass the value to the query. Probably the SQL is understanding that value as a string.