SQL Server CAST from varchar to numeric with error with error

Question

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

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

The following error occurs when starting the query: Arithmetic overflow error converting varchar to numeric data type.

If I do double CAST from varchar -> int -> numeric, CAST works. eg.

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

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

Answer 1

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

change

To be safe, with a length of 9 characters, you could have numbers from 999,999,999 to 0.1234567 . This means that you need 9 digits before the decimal point and 7 after (a total of 16). Therefore, your composition should be CAST (NUMERIC (16,7)

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

Answer 2

The cause of the error was one line in which the value "-" is the value of the field. CASTing directly in NUMERIC does not work, but CASTing to INT first forcibly returns the fields "-" 0.