ORDER BY postcode

Databases
1

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

create two columns – a VARCHAR for the alpha part, and a TINYINT for the numeric part

3

Thanks!

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

4

Have you tried?

ORDER BY postcode
5

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 :slight_smile:

Thanks

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:

order
by postcode

bazz

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.

8

good luck loading even a medium sized customer address table into memory…

:wink:

9

spoil-sport :wink:

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