13

Recently I was working with ISNUMERIC in SQL Server, when I encountered a problem, which led to finding this snippet of code.

SELECT ISNUMERIC('.')

This returns 1, as in true, shouldn't this return 0 as in false?

Paul Talbot
  • 1,583
  • 1
  • 14
  • 28

3 Answers3

20

See IsNumeric() Broken? Only up to a point.

SELECT CAST('.' AS MONEY)

returns 0.00 (though the cast fails for int and float)

ISNUMERIC just checks that the value can be cast to any one of the numeric datatypes which is generally useless. Usually you want to know whether it can be cast to a specific type.

Additionally it doesn't even seem to do that task correctly for all possible inputs.. ISNUMERIC(' ') returns 0 despite casting successfully to both int and money. Conversely ISNUMERIC(N'８') returns 1 but does not cast successfully to anything that I tried.

Some useful helper functions for that are here IsNumeric, IsInt, IsNumber.

SQL Server 2012 introduced TRY_PARSE and TRY_CONVERT that help with this greatly.

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 3
    exactly. Consider the slippery slope: `10` is ok, `10.0` is ok, `10.` is ok... (_starting to slide_) so `.1` should be ok... and `.0` and... yes `.` is ok too. [Being generous to input parsing](https://plus.google.com/114034641416688387662/posts/8bJZnLFxAfi) has it's downsides sometimes. – sehe Sep 13 '11 at 10:28
  • Thanks for that, roll on Denali! – Paul Talbot Sep 13 '11 at 10:51
  • In my case it was returning 1 to dash input (thinking it's -1). My workaround was to prepend a '0' in the func call, like this: `ISNUMERIC('0'+input)`. This way, the func tries to parse '0-', returning 0 then. – JwJosefy Apr 21 '19 at 01:00
  • What about this? It blows my mind.... select isnumeric('-') returns 1 as well :( – Juan Velez Jun 05 '20 at 14:18
  • 1
    @JuanVelez - `SELECT CAST('-' AS INT) ` does return `0` so that does cast successfully to at least one number type (which is all this function checks) – Martin Smith Jun 05 '20 at 17:22
2

Because "." is used in a decimal number !

see here

isnumeric for '-' & '.' Why isnumeric('-') & isnumeric('.') returning 1?

Answer: Because "-" means negative and "." is used in a decimal number. I have no clue why they named it ISNUMERIC though. They should have named it, ISNUMBERRELATED.

Arsen Mkrtchyan
  • 49,896
  • 32
  • 148
  • 184
0

I think it also interprets a number of other non-numeric fields as numeric, there's further info here -

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

ipr101
  • 24,096
  • 8
  • 59
  • 61