How Do I Add Two Values Of Type Varchar In Sql Server?

Question

1.00/5 (2 votes)

See more:

I have one table name SpaceRequest showing below

ID(numeric) Name(varchar) Requested_Space(varchar) Status

1 Pranav 1GB True

i want to update values to the column Requested_Space.
i.e 2GB + 1GB should make 3GB in all...through query
how i can do it??
HELP..:)
thanx in advance

Posted 9-May-14 20:41pm

Pranav-BiTwiser

Add a Solution

3 solutions

Solution 1

Don't.

While it is technically possible, it's a pain to do, and should be avoided.
It can be done: you have to parse each string yourself, extract the numeric part and the "qualifier" ("GB" in your example) then convert the numeric to a number and "normalize" it to a "proper" number by working out what the qualifier means (so that 3GB + 3000MB will make 6GB, not 3003GB) then convert it back to an appropriate string again afterwards. Not nice, and very awkward.

Instead, store your value as a numeric value, preferably to a "rational" base value: KB for example.
So your 3GB value would be stored as 3 * 1024 * 1024 KB or 3145728 as a number.
You can then add numbers easily, and format them for display very simply when you need to.

Posted 9-May-14 21:45pm

OriginalGriff

Solution 2

OriginalGriff has given you a very good advice against doing it this way. If you insist, then take a look at the following query, see how complicated it is that you have to convert varchar to number for arithmetic operation then to have it converted back to varchar in order to concatenate the 'GB' to it:

SQL

update table1 set requested_space =
convert(varchar, (convert(int, substring(reverse(requested_space), 3, len(requested_space))) + 2)) +
'GB';
select * from table1

While it is technically possible, does not mean we have to do it, when it is not efficient.

Posted 9-May-14 21:56pm

Peter Leow

Updated 9-May-14 21:57pm

v2

Comments

Pranav-BiTwiser 10-May-14 5:16am

thanx..:)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pranav-BiTwiser · Accepted Answer · 2014-05-09T23:16:00

Solution 3

i have solved it by myself....thanx..:)

update UserRegistration set AllotedSpace = CAST((select SUBSTRING(AllotedSpace,0,2) from UserRegistration where ID=7) + 2 as varchar)+' GB' where ID=7

Posted 9-May-14 23:16pm

Pranav-BiTwiser