Update values in a table - 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

Update values in a table

Author

Topic

faith2011
Starting Member

9 Posts

Posted - 2011-09-28 : 15:49:59

I need to update the zip codes in a table where the "0" was left off. They are not listed as 4435,4429,4469 and should be 04435,04429,04469.

Need help with an update query in addition to reformat the current values.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-28 : 16:57:53

assuming your zip code column is some type of character based datatype - perhaps this:


update z set
       z.zipcode = right('00000' + z.zipcode, 5)
from   <yourTable> z
where  len(zipcode) < 5

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 22:51:46

if you're declaring zip code as integer then leading 0 will not be stored. so you should be storing them as varchar if you want to store it with leading 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-29 : 01:20:28

Different methods by which we can Pad zeros to a column - http://vadivel.blogspot.com/2006/06/padding-leading-zeros.html

As mentioned in the earlier post you gotto have a VARCHAR column if you want to retain the leading zeros. Ideally zipcode has to be VARCHAR(10) only but just in case you have it as an INT type then may be you can create another computed column to store it automatically. Something like this:

CREATE TABLE tblPaddingZeros
(
ID INT IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(20),
LastName VARCHAR(20),
Zip INT,
ZipCode AS RIGHT('00000' + CAST(Zip as VARCHAR), 5)
)
GO

Insert into tblPaddingZeros (FirstName, LastName, Zip) Values ('Vadivel', 'M', 4435)
GO
Insert into tblPaddingZeros (FirstName, LastName, Zip) Values ('Alpha', 'A', 4429)
GO
Insert into tblPaddingZeros (FirstName, LastName, Zip) Values ('Beta', 'B', 4469 )
GO

SELECT * FROM tblPaddingZeros
GO

Best Regards
Vadivel

http://vadivel.blogspot.com

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-09-29 : 04:56:39

Hi,

Please Refer below links

http://blog.sqlauthority.com/2009/03/16/sql-server-udf-pad-ride-side-of-number-with-0-fixed-width-number-display/

http://blog.sqlauthority.com/2009/03/10/sql-server-pad-ride-side-of-number-with-0-fixed-width-number-display/

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources