SQL Server CAST from varchar to numeric fails with error

Question

I have a column of data that contains 9 digits of numeric values, but the column is defined as a varchar. I need to CAST the field to a numeric value so I can divide the sum of the column by 100. e.g.

select CAST(field1 as numeric(9,2)) / 100 from table;

I get the following error when running the query: Arithmetic overflow error converting varchar to data type numeric.

If I perform a double CAST from varchar -> int -> numeric, the CAST works. e.g.

select CAST(CAST(field1 as int) as numeric(9,2)) / 100 from table;

Is there a reason why the single CAST from varchar -> numeric results in a SQL error, but the double CAST works?

Answer 1

If your field contains 9 digits, it could be a max of 999,999,999. My original thought was that your cast should be CAST (NUMERIC 11, 2)

edit

To be on the safe side, with 9 character length, you could have numbers ranging from 999,999,999 to 0.1234567. This means you need 9 digits before the decimal point and 7 after (total 16). Therefore your cast should be CAST (NUMERIC (16,7)

select CAST(field1 as numeric(16,7) from table;

Answer 2

The cause of the error was one row that had '-' as the value for the field. CASTing directly to NUMERIC doesn't work, but CASTing to INT first forced the '-' fields to return 0.