CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

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

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

  • Comments 2

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

Leave a Comment
  • Please add 6 and 5 and type the answer here:
  • Post
Page 1 of 1 (2 items)