adding 2 numbers

Question

adding 2 numbers

kevin4u06

SSCrazy

Points: 2093
More actions
December 15, 2010 at 8:02 am

#244331

Hi all,
This must be a silly question....Is there a way to add 008 + 1 and get the result as 009 instead of just 9

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Kevin it has to do with datatypes; '008' is not numeric, it's a string, same as 'abc', but when you add it to an integer, it might get converted to an integer.

if you mix the datatypes, SQL goes with the a specific order of implicit conversion...changing everything to integers or strings, for example

SELECT '008' + '1 '--'0081'

SELECT '008' + 1 --9

SELECT 008 + '1' --9'

SELECT 008 + 1 --9

SELECT '008' + '1'--'0081'

SELECT CONVERT(int,'008') + CONVERT(int,'1')--9

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kevin4u06 SSCrazy Points: 2093 More actions · Answer 2

Thanks lowell. I tried using all the string functions to acheive this but canot get the result.

Lowell SSC Guru Points: 323518 More actions · Answer 3

to get preceeding zeros, you have to decide on the length you want(you said 3 digits in this example)

and do something like this:

SELECT RIGHT('00000000' + CONVERT(VARCHAR,(8 + 1)),3)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kevin4u06 SSCrazy Points: 2093 More actions · Answer 4

I tried the following code

select left('00'+cast(substring('008', 2,2)+1 as varchar(10)),3)

it hold good only for 000 to 008

of i add 009 + 1 using the above code it will give me 001 instead of 010

kevin4u06 SSCrazy Points: 2093 More actions · Answer 5

thank you ver much lowell this your code helps me.....

SkyBVI SSCoach Points: 15128 More actions · Answer 6

plz exp[lain clearly, i cant understand

Regards
Sushant Kumar
MCTS,MCP