MySQL排序英国邮政编码

Question

I currently have a simple MySQL select with an order by on a column called postcodes. These postcodes are UK.

It is currently outputting in this order: SE1, SW1, SE10, SE11, SE2, SW2, SE3. I know using ABS() would correct the number ordering, but I'm not sure how I would do it in this case because there are letters as well as numbers.

I would like to display them in the following order:
SE1, SE2, SE3 SE10, SE11, SW1, SW2

Thanks for any help.

Answer 1

You could add a few extra columns to the table to contain the snippets within them and return the order by using the tertiary columns to sort by. For example:

PostCodeTable
|---------------------------------------------|
|postCode| codePart1 | codePart2 | codePart3  |
|char()  | char()    | int()     | whatever() |
|---------------------------------------------|
| SE1    | SE        | 1         |            |
| SW1    | SW        | 10        |            |
| SE10   | SE        | 10        |            |
| SE11   | SE        | 11        |            |
| SE2    | SE        | 2         |            |
| SW2    | SW        | 2         |            |
| SE3    | SE        | 3         |            |
|---------------------------------------------|

Your query might be:

Select
    postCode
from
    PostCodeTable
order by
    codePart1,
    codePart2
    // etc etc as needed.