July 26, 2010 at 5:32 am
Hi, Plz help me on following type of scinario.
I m having a table custinfo with the coloumns Fname,Lname,Add1,Add2 and Pincode.In the current table Pinocode entries r not proper.For example they r like 415506,11,004,400 like that.Means not exact 6 digits.But while migrating it into new table i want to update the coloumn Pincode with exact 6 digits.For that if pincode is less than 6 character then i have to put 0's. Eg. If Pincode is 41 then while migration i have update it as 000041.If it is 3 then 000003 like this.If anybody has idea then plz help me.:-)
July 26, 2010 at 6:22 am
Try this.. The code below has Data as your table and PinCode, the column.
declare @table table (LengthOfPincode int, AddZeros varchar(10))
insert into @table values (1,'00000')
insert into @table values (2,'0000')
insert into @table values (3,'000')
insert into @table values (4,'00')
insert into @table values (5,'0')
update Data
set PinCode = AddZeros + PinCode
from
Data inner join @table on len(PinCode) = LengthOfPincode
where len(PinCode) < 6
July 26, 2010 at 6:38 am
VM-723206 (7/26/2010)
Try this.. The code below has Data as your table and PinCode, the column.declare @table table (LengthOfPincode int, AddZeros varchar(10))
insert into @table values (1,'00000')
insert into @table values (2,'0000')
insert into @table values (3,'000')
insert into @table values (4,'00')
insert into @table values (5,'0')
update Data
set PinCode = AddZeros + PinCode
from
Data inner join @table on len(PinCode) = LengthOfPincode
where len(PinCode) < 6
What datatype is custinfo.Pincode ?
select '0000' + '5'
select '0000' + 5
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply