Canadian Postal Code Format

  • Hello, all:

    I'm fairly new to T-SQL, so I'm sure there's a relatively simple way of doing this, but I sure don't know it! I have a zip code field (which I inherited, by the way, so none of this is my fault!) into which several Canadian zip codes have mistakenly been entered. All the location data I really need for records outside the US is the country name, so I'd like to locate the Canadian zip codes and just enter 'CA' into my CountryCode field for those records. How do I search for any zip code in X#X#X# format?

    Thanks!

  • One way would be to get a full list of Canadian postal codes and use that. That's a bit of overkill, though.

    Canadian postal codes are formatted with three characters, then a space, then three characters. You could look for that pattern. Will that do what you need? Or were they stored without spaces?

    The other thing you could do is look in your State column (assuming you have one), and see if you have anything that's not a US state, and set the postal code to null/blank for those. That might be easier than some pattern-recognition system.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Hi, GSquared:

    I actually had hoped to just get a list of postal codes, but the cheapest one I could find was $100, and, working at a non-profit, I'm trying to do this on the cheap! 🙂

    Unfortunately, the codes were stored without spaces, so I can't do that either. Also, there are some country codes mixed in with the zip codes, and their State fields are null, so I can't use that as a criterion. *sigh*

    Thanks for the suggestions! 😀

  • Can you do a reverse lookup on it? Find all the rows where postal code "like '[0-9][0-9][0-9][0-9][0-9]' or like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'", and then exclude those from your lookup? Those are the valid formats for US Zip codes, and Canadian postal codes are never formatted like that.

    Something like this:

    select *

    from dbo.MyTable

    where postalcode not like '[0-9][0-9][0-9][0-9][0-9]'

    and postalcode not like '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]'

    I think that will get you everything that isn't a US Zip code, and won't get you any that are US Zip codes. Try something like that, see if it at least narrows it down nicely for you. Then you can add more criteria to the Where clause, if you need to exclude more values to get just Canadian postal codes.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • The Canadian Postal Code has the following format: A0A 0A0. If there are no spaces in your data, then you would look for the format: A0A0A0. Perhaps -> like '[A-Z][0-9][A-Z][0-9][A-Z][0-9]'.

  • Thanks to you both! Exactly what I needed!! 😀

  • Glad to help.

  • If you want to be super accurate you can use this as your like string. It includes only the proper letters currently included in Canadian postal codes (taking in account the difference in the first letter):

    '[a-eghj-npr-tvxy][0-9][a-eghj-npr-tv-z][0-9][a-eghj-npr-tv-z][0-9]'

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply