Search code examples
sqlsql-server-2005isnumeric

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?

Solution

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