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)
SQL Server Senior Escalation Engineer