Quick SQL Select Trick to add leading zero for zip codes
Working on a Salesforce.com to ACT! by Sage conversion today and realized the CSV files from Salesforce didn’t create a true zipcode column. This particular client had a lot of zipcodes with leading zeroes – which of course were truncated during the import into SQL.
So, here’s the trick for the day. In SQL Management Studio, I created a SQL statement (with Don’s help of course) to interrogate the zipcode field and then add a Zero if the field was less than 5 characters. Here’s the statement – you will obviously need to plug in the right table and field names to make it work in your environment.
UPDATE dbo.[Contact]
SET [MailingPostalCode] = ’0′ + LTRIM(RTRIM([MailingPostalCode]))
WHERE LEN(LTRIM(RTRIM([MailingPostalCode]))) <5
The first line above sets the table name. The second line defines the field to be updated (in the SET) and assigns to that field a zero (that’s the ‘o’ part) plus the remaining part of the Zipcode field.
The SQL Trim feature functions (LTRIM and RTRIM) are useful to ensure we are only getting clean data – i.e. these functions remove leading and trailing blanks from a string. For example, the function LTRIM(‘ 1456′) returns:’1456′ with no leading space. You do this to ensure you really only have clean numbers without spaces to concatenate with the leading zero you are adding.
The final line says do this only when the Zipcode is less than 5 (<5).
So, import a file into a database and find out you are missing zeroes? Well, then import into Access or SQL and run this nifty command. Works like a charm.
Once again, thanks to my SQL king, Don.
Alternative: equivalent works in Rexx, not sure about SQL but: RTRIM(’00000′||MailingPostalCode)
Tim Hare
June 16, 2010 at 1:06 am
Hm, interesting. will have to try it. May not work since Rexx is it’s own thing.
pregen
June 16, 2010 at 1:17 am
Last time I checked (which is a couple of minutes ago) the REXX VM expression would be
RIGHT( MailingPostCode, 5, ’0′)
But that is VM, which is subtly different from iSeries, which was subtly different from OS/2 REXX in its day. Even IBM had multiple “standards” for their standards.
Don
June 16, 2010 at 2:09 am
Don I was trying to convert the Rexx syntax to the SQL you posted. The RIGHT function probably would work too, I always forget about the third operands on some of those Rexx functions.
There’s also the Rexx DB2 interface, and I think Regina has an SQL interface for one or two.
Tim Hare
June 16, 2010 at 3:03 am
In any event, if you’re doing it a LOT, it’s better to invoke only one function ,with the constant. Of course on today’s machines no one is really worrying about CPU cost of a function any more. You burn more CPU drawing a following mouse trail in your GUI, probably.
Tim Hare
June 16, 2010 at 3:53 pm
[...] Quick SQL Select Trick to add leading zero for zip codes June 2010 5 comments 3 [...]
2010 in review « Pat Egen’s Weblog
January 3, 2011 at 3:20 am
how about:
select right(100000 + coalesce(zip, 0), 5) as adjusted_zip
from table
eddy
September 13, 2011 at 4:34 pm