Share via

IsNumeric Returns (1/TRUE) and I Expected (0/FALSE)

  • Article

The string '100,200,300' has fooled many a customer and support engineer alike.

select cast('100,200,300' as bigint)

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

One might argue that this is a bigint value.  However, the T-SQL parser does not allow comma separators in a bigint conversion so you get the proceeding error.

So why would IsNumeric return a 1 for the value.   The simple answer is a money value.  Commas are allowed in a money value so this would result in a valid numeric conversion when cast as a money value.

select cast('100,200,300' as money)

---------------------
100200300.00

Bob Dorr
SQL Server Senior Escalation Engineer

Comments

  • Anonymous
    August 25, 2008
    PingBack from http://hoursfunnywallpaper.cn/?p=2932

  • Anonymous
    August 25, 2008
    This is also true when just a decimal point ( full stop ) is set for conversion. select isnumeric('.')  will return true select convert (int,'.')  will fail Seen this cause a few "bugs" in my time.