I am a little confused here. SELECT ISNUMERIC('.') returns 1, that is '.' string is considered to be of a numeric convertible type. However, SELECT CONVERT(numeric, '.') returns error message: "Error converting data type varchar to numeric." Looks like a controvercy to me... I would say that a correct ISNUMERIC('.') value should be 0 (zero). What do you think?
asked Sep 09, 2010 at 11:50 AM in Default
There is no controvercy here. The mere fact that isnumeric returns true does not guarantee that the value is converible to numeric. This is due to the isnumeric implementation, which calls for trying to check whether the input can be represented as a numeric value in any of the available settings. For example, the input 123.456,56 is not convertible to decimal number with en_US settings, but isnumeric for this input is 1 because it happens to be a valid number in any environment where dot is used as a thousand separator and comma as decimal point. Same thing applies to 123 456,56 (isnumeric is 1 because a space is used as a thousand separator in some environments). On the other hand, it is possible to get isnumeric = 0 while the input is convertible. For example
Though isnumeric returns false for the above, it still can be cast as integer (but not as decimal, decimal still errors out). Here is another obscure example:
Where did the * come from? Well, it came from internal implementation of the int data type, which returns * if the integer value does not fit into varchar variable :)
The bottom line: any cave man attempts to safely convert input values to decimal if such inputs passed isnumeric validation are failure prone.
Just wanted to clarify the difference
This has been discussed in detail at
Confusingly there is a numeric data type called
is functionally equivalent to
which when written like that is clear why it fails with an error.
answered Sep 10, 2010 at 01:50 AM
Kev Riley ♦♦
Since a '.' could be a decimal or a period in a sentence, it's hard to say. Maybe it should return NULL since it could be either.
I think it returns 1 so that errors (or false alarms) will not be thrown when evaluating numbers with decimals. I would side with 1 on that count.
p.s., '-' and '+' also return 1.