I’ve had a problem that’s been nagging me for the last 5 years. Not kidding – I’ve created lots of workarounds, but what I really wanted to do was to create a way to sort a list of UK postcode area codes so they “look right”. UK postcodes have an area/district code as the front half, a space, then the sector/unit code. Typically, if someone is looking for a postcode for a business, they’re concerned with the area/district.
My problem has been that sorting this: S1,S2,S3,…S10,S11,SW1,SW2,…SW10,SW11 would end up like this:
S1
S10
S11,
S2,
S3,
…
SW1,
SW10,
SW11,
SW2
…
Not what I want. I want this:
S1,
S2,
S3,
…
S10,
S11,
SW1,
SW2,
SW10,
SW11
…
mysql regexp returns true or false, and doesn’t do backreferences, otherwise this would be pretty simple. I came up with this:
select code from postcodes order by if((code regexp '^[A-Z][0-9]'), left(code,1), left(code,2)), if((code regexp '^[A-Z][0-9]'), right(code,length(code)-1)+0, right(code,length(code)-2)+0);
The conditional order by does this: If the code starts with one character followed by one digit, order by the portion of the string to the left of character position 1, otherwise sort by the portion of the string left of character position 2. Then if the code starts with one character followed by one digit (same test as the first one), order by the portion of the string starting from the end (right side) and include all characters except the first (length(code)-1), otherwise order by all characters except the first 2. This second condition pulls the numeric portion off. However, right() returns a string, so I forced the interpretation of it as a numeric (kludged it) by adding zero to it.
I hope this is helpful to somebody. If you’ve got a better idea, please comment.
