panda_coder — 2010-02-12T11:02:10-05:00 — #1
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.
The sql I have worked out so far is:
ORDER BY length(postcode), postcode
which works for the same first 2 chars, like:
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!
r937 — 2010-02-12T11:14:41-05:00 — #2
create two columns -- a VARCHAR for the alpha part, and a TINYINT for the numeric part
panda_coder — 2010-02-12T11:40:44-05:00 — #3
I was hoping for a simple addition to the current sql but I understand what you mean, best done properly
spacephoenix — 2010-02-12T11:42:47-05:00 — #4
Have you tried?
ORDER BY postcode
panda_coder — 2010-02-12T12:00:51-05:00 — #5
yes I tried this first but got results like:
I need them to be in numeric order as well like:
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
ibazz — 2010-02-12T12:02:08-05:00 — #6
IN case you would need to order by the full post code of sw22 4rf (for example), I think you'd be better to:
dr_john — 2010-02-12T13:47:20-05:00 — #7
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.
r937 — 2010-02-12T14:12:14-05:00 — #8
good luck loading even a medium sized customer address table into memory...
dr_john — 2010-02-12T14:31:52-05:00 — #9
panda_coder — 2010-02-12T15:09:19-05:00 — #10
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
$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