Why is ISNUMERIC(',') true? [duplicate]

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?

Answer 1

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.

Answer 2

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])