1

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?

Improve this question
0

3 Answers 3

Reset to default
2

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

yeah, bad database design. I have to just follow what they have. ... So you mean basically add one more SET so like this? SET MyVarCharColumn1 = ISNULL(SomeOtherVarCharColumn,0) and then next line SET MyVarCharColumn1 = MyVarCharColumn1 + 10
@Bohn . . . No. You should only set the column once.
@Bohn The answer is based on data type precedence.
1

Why not express exactly what you want?

update a
   set a.Col1 = Convert(varchar, (Convert(int, IsNull(NullIf(a.Col1, ''), '0')) + 10))
from #myTempTable a;

Broken up into steps:

  1. select value a.Col1
  2. convert empty string to null NullIf(<value>, '')
  3. convert null to zero string IsNull(<value>, '0')
  4. convert value to int Convert(int, <value>)
  5. add 10 to value (<value> + 10)
  6. convert value to string Convert(varchar, <value>)
Improve this answer

5 Comments

yeah something like that ... WILL THIS handle empty strings too? I want empty strings to be treated like null so I can replace them with Zero
@Bohn Aye, this will treat null and empty as zero; added break down.
awesome. Thanks.
@Bohn Uh wut? NullIf has been around since SQL 2005.
oh 2005 ? oh cool then we can use it .. somehow I thought it is 2016 added lol
1

Try updating set line with following code to increase it by 10

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

This will increase every cell in MyVarCharColumn1 column by 10, and if you want to add special conditionals you can use WHERE

EDIT 2:

I added ISNULL now, so I now code will first run line to see if its number, if not it will set it to 0, if it is number, it won't set it to zero and will move into the next line which is increasing by 10

Improve this answer

5 Comments

When giving a solution, always show the whole work that the OP needs, not just a single line. And explain what you did and where improvements could be done.
I hope it is good now, and thank you for helping me, since I'm still learning about stack overflow
What happened to ISNULL that was originally there? I want it to still be able to handle and treat null and empty strings as value ZERO
I think you can try now, since I added ISNULL
The update statement doesn't allow set more than once.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.