Append leading zeros to zipcode field

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

SQL Server 2008 Forums

Transact-SQL (2008)

Append leading zeros to zipcode field

Author

Topic

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2014-03-19 : 18:04:17

Our client sent us a data file to import, but I noticed their zipcodes are missing leading zeros (i.e. zip code 00138 is just 138).

We've already imported the data but need to fix these zipcodes in our database. Any zips with 3 characters needs 2 zeros appended, any zips with 4 characters, needs 1 zero.

how can I do this?
Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-19 : 18:25:19

Here are a couple of ways

SELECT RIGHT('00000' + '138', 5),
	RIGHT(REPLICATE('0', 5) + '138', 5)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-20 : 16:19:25

quote:
Any zips with 3 characters needs 2 zeros appended

Fyi, technically the zeros need to be "prepended", not appended

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-03-21 : 07:17:06

The following will reach your criteria......

CREATE TABLE prepend(Number VARCHAR(MAX) )

INSERT INTO prepend VALUES(159),(567),(2344),(1234),(5690),(4449)

UPDATE prepend set Number = (CASE WHEN Len(Number)>3 THEN '0'+CAST(Number AS VARCHAR(50) ) WHEN LEN(Number) = 3 THEN '00'+CAST(Number AS VARCHAR(50)) END)

Murali Krishna

You live only once ..If you do it right once is enough.......

Goalie35
Yak Posting Veteran

81 Posts

Posted - 2014-03-21 : 10:39:06

Thanks guys. This is what I ultimately went with:


update myTable
set zip = (CASE LEN(zip) WHEN 4 THEN ('0'+ zip) WHEN 3 THEN ('00'+zip) else zip END)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources