#1
  1. For POny!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Amsterdam
    Posts
    416
    Rep Power
    115

    Range of Doom Postal codes


    Hi guys, I am the unfortunate man to have the task to extract from a database the email addresses that fall in a range of postal codes. The data is horrible and the only consequent thing which I want to sort on are the first 4 digits.

    Example data
    8763 BA
    2451XA
    1341-BC

    For instance get all email addresses that fall in a range between 1200 and 1400. This is what I tried:

    Code:
    SELECT email, pcode FROM chaos_table_of_doom WHERE pcode BETWEEN
    (SUBSTRING(pcode,1,4) = 1200) 
    AND
    (SUBSTRING(pcode,1,4) = 1400)
    LIMIT 0, 200;
    This doesn't quite work (the pcodes don't match)
    Anyone maybe knows a good way to do this (apart from firing the guy that created this table)

    -EDIT: ok i think I fixed it but if someone has a better method let me know
    Working code used:
    Code:
    SELECT email, pcode FROM chaos_table_of_doom WHERE SUBSTRING(pcode,1,4) >= 1200
    AND
    SUBSTRING(pcode,1,4) =< 1400
    LIMIT 0, 200;
    Last edited by aeternus; December 10th, 2012 at 02:29 PM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,030
    Rep Power
    4210
    your solution is fine, you could also use this --
    Code:
    WHERE SUBSTRING(pcode,1,4) BETWEEN 1200 AND 1400
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. For POny!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Amsterdam
    Posts
    416
    Rep Power
    115
    Originally Posted by r937
    your solution is fine, you could also use this --
    Code:
    WHERE SUBSTRING(pcode,1,4) BETWEEN 1200 AND 1400
    Thanks for sharing mate!

IMN logo majestic logo threadwatch logo seochat tools logo