How do I update a varchar column that has a number by adding another number?

Question

I have a column that is varchar(80) and is storing dollar amounts (no $ sign). I would like to update the value by adding another amount, e.g. the existing amount is 96.73 and I want to add 1.00 to make 97.73.

I have tried cast and convert without any luck. I have tried:

set CAVALUETEXT = convert(INT, CAVALUETEXT) + 1.00
set CAVALUETEXT = cast(cavaluetext as int) + 1.00

and get the same error:

Conversion failed when converting the varchar value '96.73' to data type int.

I think I am using SQL Server 2008.

Answer 1

You can use CONVERT(float, [YOUR Varchar]). For example CONVERT(float, '0.123') convert it to a float 0.123. This only works on SQL Server.

Answer 2

You could cast to DECIMAL, do the arithmetic, and then cast back to VARCHAR:

UPDATE yourTable
SET CAVALUETEXT = CAST(
    CAST(CAVALUETEXT AS DECIMAL(19,2)) + 1.00 AS VARCHAR(21));

But the much better approach here would be to stop storing currency amounts, especially amounts on which you might need to do arithmetic, in a text columns. Use a proper type for this like DECIMAL(19,2).