SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast Panda Coder's Avatar
    Join Date
    Nov 2005
    Location
    Tarifa, Spain
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ORDER BY postcode

    Hi,

    I have short postcodes in a column 'postcode'. By short I mean SW1, BN2, BN22 etc, just the first part.

    I need to list them in this order for eg.
    BN2
    BN22
    CR1
    CR12
    SW1
    SW12

    The sql I have worked out so far is:

    ORDER BY length(postcode), postcode

    which works for the same first 2 chars, like:
    BN2
    BN22

    but the whole list is not in order with sw coming before bn etc. I know there must be a way... Does anyone have any ideas please, or pointers to a tutorial. I have searched the Mysql site but can't find a way, or can't get my head round the general instructions to make a way.

    Any help much appreciated!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    create two columns -- a VARCHAR for the alpha part, and a TINYINT for the numeric part
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast Panda Coder's Avatar
    Join Date
    Nov 2005
    Location
    Tarifa, Spain
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!

    I was hoping for a simple addition to the current sql but I understand what you mean, best done properly :-)

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Panda Coder View Post
    Hi,

    I have short postcodes in a column 'postcode'. By short I mean SW1, BN2, BN22 etc, just the first part.

    I need to list them in this order for eg.
    BN2
    BN22
    CR1
    CR12
    SW1
    SW12

    The sql I have worked out so far is:

    ORDER BY length(postcode), postcode

    which works for the same first 2 chars, like:
    BN2
    BN22

    but the whole list is not in order with sw coming before bn etc. I know there must be a way... Does anyone have any ideas please, or pointers to a tutorial. I have searched the Mysql site but can't find a way, or can't get my head round the general instructions to make a way.

    Any help much appreciated!
    Have you tried?

    Code SQL:
    ORDER BY postcode
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SitePoint Enthusiast Panda Coder's Avatar
    Join Date
    Nov 2005
    Location
    Tarifa, Spain
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    yes I tried this first but got results like:

    KT1
    KT10
    KT17
    KT2
    KT20
    KT21

    I need them to be in numeric order as well like:

    KT1
    KT2
    KT10
    KT17
    KT20
    KT21

    That's why I tried the: ORDER BY length(postcode), postcode
    which works if they were all KT but goes out when you introduce other prefixes.

    Easy enough to add 2 more cols for this situation though so I have taken this route for the moment. If there is another way, I am always looking to learn :-)

    Thanks

  6. #6
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    IN case you would need to order by the full post code of sw22 4rf (for example), I think you'd be better to:

    order
    by postcode

    bazz

  7. #7
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You should be able to write a user-defined sort in PHP, using usort() and strnatcmp() - the data should be in an array. That nat in the second function is for natural sort, which is what you are after.

    Or so I read this morning when I decided to do some revision of PHP...
    It doesn't look difficult - not that I've tried it before.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dr John View Post
    You should be able to write a user-defined sort in PHP, using usort() and strnatcmp() - the data should be in an array.
    good luck loading even a medium sized customer address table into memory...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    spoil-sport

  10. #10
    SitePoint Enthusiast Panda Coder's Avatar
    Join Date
    Nov 2005
    Location
    Tarifa, Spain
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok all done, added 2 cols and now it sorts perfectly using:

    ORDER BY postcode_prefix, postcode_suffix

    I should add that the data is added in a PHP cms during one process so I added

    Code:
    $postcode_prefix = preg_replace("/[^A-Z]+/","",$postcode);
    $postcode_suffix = preg_replace("/[^0-9]+/","",$postcode);
    after the postcode validation and so the two extra cols are populated there and are now available for the sort when the data is listed in a report.

    Thanks all for your help


Bookmarks

Posting Permissions

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