add a fixed value (integer) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

add a fixed value (integer)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-30 : 09:30:35

philippe writes "Hello ,
i am just starting with sql...

I have a table: Table_user

Inside a column user_no (integer)
with value like 35678 (about 8000 rows)
I would like to modifie all rows of this Table_user.user_no
with a value of 60 at the begining of each value.
For example : before 35678 will be 6035678
Its'a concatenation,but it's an integer value .
so do i need to convert tehm to varchar first

I will appreciate some tips...."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-05-30 : 09:31:40

UPDATE Table_user SET user_no=6035678 WHERE user_no=35678

or

UPDATE Table_user SET user_no=user_no + 6000000 WHERE user_no=35678

svicky9
Posting Yak Master

232 Posts

Posted - 2006-05-30 : 11:16:39

hi Rob,I dont think thats the correct query.
if you want to concatenate 60 to each and every userid

try

update table_user set user_no = convert(int,'60'+(convert(varchar(20),user_no)))

cheers
Vic

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-30 : 14:06:06

Another option is to not stray away from integers, which might be more confusing, but should perform a bit quicker (though I've not tested it

--data
declare @Table_user table (user_no bigint)
insert @Table_user
          select 35678
union all select 1
union all select 2
union all select 123
union all select 1213123
union all select 1231554575

--calculation
update a set user_no  = 60 * power(cast(10 as bigint), floor(log10(user_no )) + 1) + user_no
from @Table_user a

select * from @Table_user

--results
user_no              
-------------------- 
6035678
601
602
60123
601213123
601231554575

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-31 : 01:58:57

quote:
Originally posted by svicky9

hi Rob,I dont think thats the correct query.
if you want to concatenate 60 to each and every userid

try

update table_user set user_no = convert(int,'60'+(convert(varchar(20),user_no)))

cheers
Vic

The quesioner specified that user_no column is of interger datatype and there is no need to covert it to varchar then integer

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-05-31 : 02:10:19

Nice to see high school math hasn't been wasted on you Ryan.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources