Using SQL Server Arithmetic Operators on varchar Field

Question

I have a third party data source that has a varchar(255) column that contains only numbers. This column is the cost of an item. I would like to be able to multiply this column by a factor (2.3) to get a default selling price but I cannot apply arithmetic operators on the varchar column and the CAST function is producing an error when combined with the arithmetic operators.

This statement produces the following sample results:

SELECT 
    AverageCost
FROM 
    dbo.inventory

• 169
• 32.5
• 55.25
• 8.42
• 295
• 0
• 3.33333

This satatement:

SELECT 
    AverageCost * 2.3 AS Price
FROM 
    dbo.iteminventory

Produces this error

Arithmetic overflow error converting data type numeric

Writing the statement as follows to cast as a decimal produces no errors but adding the arithmetic operator into this statement has eluded me, my attempts have all resulted in errors.

SELECT 
    CAST(AverageCost AS Decimal(10, 2)) AS Price
FROM 
    dbo.inventory

Thanks in advance for you help.

Answer 1

If 2012+ use Try_Convert()

Try_Convert() will catch any conversion errors as a NULL

SELECT try_convert(float,AverageCost) * 2.3 AS Price 
FROM   dbo.inventory

Returns

Price
388.7
74.75
127.075
19.366
678.5
0
7.666659

To see the values which may be causing the conversion error

Select *
 From  iteminventory
 Where try_convert(float,AverageCost) is null