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.
<!-- Begin Edit
Here is the sample of such attempt. I will create a table, populate it with few records and then try to sanitize the data and then select it. Possibilities are endless and never bullet proof though :(
One more thing I would like to point out. Cast and Convert to decimal without specifying precision and scale is not a good idea because default scale is 0 meaning that decimal numbers are going to be converted incorrectly. For example, select convert(decimal, '123.4567') returns 123 and this is probably not what is expected. select convert(decimal(10,2), '123.4567') returns 123.46 with just a small rounding error and select convert(decimal(10,2), '123.4567') returns 123.4567 not loosing any precision at all.
End Edit -->Oleg
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 ♦♦
Kev Riley has a good idea using PATINDEX here too: [Kev's Solution].
(The link was too long for a comment.): http://ask.sqlservercentral.com/questions/608/how-to-select-records-with-letters-in-an-nvarchar-field-that-should-only-be-numbers/613
answered Sep 09, 2010 at 01:56 PM
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.