Converting string to integer and then converting back to string

I have some update statements like this:

UPDATE tmp
    SET MyVarCharColumn1 = ISNULL(SomeOtherVarCharColumn,0)
FROM #myTempTable tmp;

Note that MyVarCharColumn1 is of type VarChar() but in real life it only will have numeric-like values.

Now I want to change it to ADD a numeric value like integer value of 10 to final value of MyVarCharColumn1 and then again convert it back to VARCHAR().

So for example if currently it is “23” now I want it to be “33” .

What’s a safe way of casting in that update statement that could also handle empty strings?

Answer

If it is really a number, you can do:

UPDATE tmp
    SET MyVarCharColumn1 = MyVarCharColumn1 + 10
FROM #myTempTable tmp;

If you are already setting the value from another column:

UPDATE tmp
    SET MyVarCharColumn1 = COALESCE(SomeOtherVarCharColumn, 0) + 10
FROM #myTempTable tmp;

SQL Server will interpret the + as addition, rather than string concatenation. It will then convert the first argument to a number and do the addition. It will be saved back as a string, because that is the type of the column.

I cringe suggesting this, because you should not be mixing types like this. Much better is to convert the column to the appropriate number, say by doing:

alter table tmp alter column MyVarCharColumn1 numeric(10, 0);