• 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/