John Carlson-431129 (3/25/2010)
Hugo Kornelis (3/25/2010)
John Carlson-431129 (3/25/2010)
vk-kirov (3/25/2010)
Hugo Kornelis (3/25/2010)
' ' is converted to the value 0 (run [font="Courier New"]SELECT CAST(' ' AS int);[/font] if you don't believe me).This is very interesting because [font="Courier New"]ISNUMERIC(' ')[/font] returns 0.
On the one hand, SQL Server doesn't consider whitespaces as numeric values; on the other hand, it converts whitespaces to zero integer values. This is a little bit strange 🙂
This is consistent with the explanation though. It is not considered a numeric until is implicitly converted through a numeric operation with a numeric operand. So, ISNUMERIC(' ') will return 0, but ISNUMERIC(' ' + @a + 2) will return 1.
No, it is not consistent. According to Books Online, "A return value of 1 indicates that expression can be converted to at least one of the numeric types". Since a space can, apparently, be converted at least to integer, ISNUMERIC should return 1.
Hmmm. I guess what I am trying to say, is that the ' ' cannot be numeric on its own, but only when combined with a numeric operand. (A parasite, if you will.) So, I still contend that it is consistent to return 0 if there is no numeric operand combined with it.
That said, I think it is a bad idea to even allow this to happen in the first place.
But:
[font="Courier New"]SELECT ISNUMERIC(CAST(' ' AS int))[/font]
returns 1, so the space can be cast/converted on its own to int (as we know) and, as Hugo says, ISNUMERIC(' ') should return 1 based on the BOL description. So the description doesn't match the behaviour, in this case.