Hello,

I need help in designing query to find Addresses which contains postal code.
The postal code is alpha numeric and in specific format -example (M3A P1L)

eg:

Table

123 ABc St Xyz M3A P1L
127 ABc St Xyx
128 ABc St Xyx

Query output

123 ABc St Xyz M3A P1L

Thanks

Anand

I assume you'll tell us what format your post codes take in a later post?

Hello Mike,
good to hear from you again.

The postal code is 6 digit word in alpha numeric format.
It starts with alpha-num-alph-..so on

for more info, please check this site.
h++p://www.mongabay.com/igapo/toronto_zip_codes.htm

Thanks

are you really after
If that is the case, I would seriously suggest taking a class or buying a book (I hear Rudy has a nice one, Simply SQL - The Web Site)
Dave

You provide very little information to give an adequate answer. I can't help feeling that you might do better actually trying to solve a lot of your questions yourself - in this way you'll find your knowledge will increase dramatically. Simply asking us to do every piece of work that lands on your desk is not really what the forum is about. You'll also find that people will be more inclined to help you if you at least try to help yourself.

To match the post codes that appear on that page you'll need something like the following:

Code:

select address from YourTable where address regexp 'M[0-9][A-Z] [0-9][A-Z][0-9]'

If you just want to find one particular post code then something like:

Code:

select address from YourTable where address like '%M3A P1L%'

M3A P1L is invalid

oh for cryin out pete's sake, andy, you've been jerking us around on several different forums for days now in multiple threads with severely dumbed-down nonsensical examples, and all you wanted was to validate canadian postal codes???

have you no concern for people's time you've wasted with all that folderol about "toggle case" and mixed case?

do a google search, man -- there are several sites which have a regular expression you can use

like i said before, you're on your own

Yaa mike,

your code worked for me.
trying to work on regexp commands..its magical

Thanks

more to the point, why is your data not normalized where you have one piece of data per column? or do you have bad data which you are trying to clean up?

Yes Guelphdad, It's an bad data which requires clean up.