Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2010
    Posts
    18

    Question Unanswered: Find Address containing postal code

    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
    Last edited by andy982183; 04-01-10 at 06:41.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Anand

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

  3. #3
    Join Date
    Mar 2010
    Posts
    18
    Quote Originally Posted by mike_bike_kite View Post
    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

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    are you really after
    Code:
    where postal_code = 'M3A P1L'
    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

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by andy982183
    I need help ...
    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%'

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andy982183 View Post
    The postal code is alpha numeric and in specific format -example (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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2010
    Posts
    18
    Quote Originally Posted by mike_bike_kite View Post
    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%'
    Yaa mike,

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

    Thanks
    Last edited by andy982183; 04-01-10 at 08:47.

  8. #8
    Join Date
    Mar 2004
    Posts
    480
    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?

  9. #9
    Join Date
    Mar 2010
    Posts
    18
    Quote Originally Posted by guelphdad View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •