ISNUMERIC('07213E71') = True?

Question

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?

Raffin

Answer 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: +, -,

Soissons

I don't recommend to do that in the query, because it can throw timeouts in some cases. SQL is not for that purposes. – Culley

Why would this cause a timeout? Seems like a simple solution to me – Raffin

@Curt: I assume elvenbyte means "don't use it in a WHERE clause". However, you already have a function on a column so appending a string won't affect things.. – Soissons

'0e0' will return true for fractional number too. To test for whole numbers, you should use '.0e0'. You can also test for positive numbers with this: IsNumeric('-' + Value + '.0e0') – Truda

There might be need to check the input string length as well, in order to avoid overflow errors assigning too large integer values to a datatype that is not sufficient to hold that much. Simple example with a LEN() check: DECLARE (a)value NVARCHAR(255) = '9876543210', (a)result INT ; IF ISNUMERIC((a)value + '.0e0') = 1 AND LEN((a)value) < 10 SET (a)result = (a)value ; SELECT (a)result (Yes I know, max INT would actually be 2147483647, checking with LEN() limits this to 999999999). – Bullet

Answer 2

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

Hesperus

Yes, but what he wants to know is how to pass it to the query as a just number with no notation, because SQL doesn't understands that notation. – Culley

@Culley I read it that he didn't want to treat 7213 * 10 ^ 71 as an integer (even though it is) – Kumamoto

Answer 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

Kravits

Answer 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 :)

Edme

Little late, but doesnt '%[0-9]%' match aaaaaa5aaaaa? – Becker

@marie you're right. I have edited the answer. Thank you for the warning :) – Edme

Answer 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.

Culley

Not much of an answer: how does it help OP? – Soissons