Pat Egen’s Weblog

Ramblings, chats, thoughts, ideas

Quick SQL Select Trick to add leading zero for zip codes

with 7 comments

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.

Advertisements

Written by pregen

June 15, 2010 at 4:20 pm

Posted in Uncategorized

7 Responses

Subscribe to comments with RSS.

  1. 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

  2. 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

  3. 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

  4. […] Quick SQL Select Trick to add leading zero for zip codes June 2010 5 comments 3 […]

  5. how about:

    select right(100000 + coalesce(zip, 0), 5) as adjusted_zip
    from table

    eddy

    September 13, 2011 at 4:34 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: