Why is ISNUMERIC(',') true?

Question

Why does the following Query in SQL return true?? I expected it to be false as it cannot be converted into an int or numeric value

select ISNUMERIC(',')

return 1???

select ISNUMERIC('0,1,2')

select ISNUMERIC(',,,')

also returns 1

What could I do for strict numeric checking in SQL?

I'm sure someone can explain this better, but ',' seems to be a valid value for the `MONEY` data type (value 0.0), and isnumeric seems to check for that type too. The contradictory thing though is that the empty string seems to return false for isnumeric but will also convert to the money value 0.0 just fine. — Joachim Isaksson, Jun 05 '14 at 07:06

score 7 · Answer 1 · answered Jun 05 '14 at 07:09

7

Because ISNUMERIC answers a question that nobody has ever wanted to ask:

Can this given string be converted into any of SQL Server's numeric data types? And I don't care which of those types it can or cannot be converted into.

This is why TRY_CONVERT was finally introduced into 2012 - to answer a question about a specific data type that you may care about.

For earlier versions, the best you can usually do is to use LIKE to identify the string patterns you do want to attempt to convert.

E.g. if you just want to detect digits, use Value NOT LIKE '%[^0-9]%', which asks for Value strings that do not contain any character which is not a digit.

answered Jun 05 '14 at 07:09

Damien_The_Unbeliever

234,701
27
340
448

Any explanation for the empty string converting to money just fine but returning isnumeric=0? Bug? Feature I'm mis-understanding? – Joachim Isaksson Jun 05 '14 at 07:17
@JoachimIsaksson - I think that's a bug. So far as I'm aware, the empty string will actually convert to any numeric type. – Damien_The_Unbeliever Jun 05 '14 at 07:20

score 3 · Answer 2 · edited Jun 20 '20 at 09:12

IsNumeric returns true for "," and "."

ISNUMERIC returns 1 if the string can be converted to any one of ints, numeric/decimal, float, or money. In this particular case, converting ',.' to money succeeds and returns 0.0000, therefore ISNUMERIC returns 1.

Enhancement to ISNUMERIC:

We have now added a new scalar function called TRY_CONVERT that will allow you to convert from a string to type using optional style. If the conversion fails then it will return NULL. The signature of the function is:
TRY_CONVERT(data_type[(length)], expression [,style])

score 2 · Answer 3 · answered Jun 05 '14 at 07:08

2

http://msdn.microsoft.com/en-us/library/ms186272.aspx

Microsoft: "It's not a bug, it's a feature "

Certain currency and mathematic symbols return 1 on ISNUMERIC.

answered Jun 05 '14 at 07:08

d'alar'cop

2,357
1
14
18

score 2 · Answer 4 · answered Jun 05 '14 at 07:12

As per the manual page:

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($). Also, it returns 1 for a range of datatypes like:

int
bigint
smallint
tinyint
decimal

So while bigint accepts money values like 230,000, a comma(,) also falls in the set of character which is considered to be a part of a numeric datatype similar to a dot(.) which is a character but is a part of the decimal datatype.

Why is ISNUMERIC(',') true?

4 Answers4

Linked