January 14, 2009 at 1:16 pm
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!
January 14, 2009 at 1:27 pm
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
January 14, 2009 at 1:32 pm
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! 😀
January 14, 2009 at 1:48 pm
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
January 14, 2009 at 3:54 pm
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]'.
January 15, 2009 at 11:47 am
Thanks to you both! Exactly what I needed!! 😀
January 15, 2009 at 12:01 pm
Glad to help.
January 18, 2012 at 2:44 pm
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