• hardik.doshi (8/27/2010)


    ISNUMERIC is returing 0 so implicit conversation is not converting BLANK to ZERO. Therefore the reason behind this is something else.

    Not so. You can easily prove that SQL Server does in fact convert blank strings to 0 with the following statement:

    select cast('' as int)

    ISNUMERIC doesn't work in this case because it doesn't actually try the conversion. It uses a different code path to determine whether a string is convertible to numeric, and that codepath does not follow exactly the same logic as the conversion code path. As Hugo said, this is best characterized as a bug in ISNUMERIC.

    For a better example of why you shouldn't depend on ISNUMERIC, try the following two queries:

    select cast('.' as float)

    select isnumeric('.')

    (edited to make final example more clear)