Natural sort, letters first

[silkfire]

I know that if you want to make a natural sort with numbers first you just do 'ORDER BY field + 0' but what is it's the other was around and the letter prefix is of variable width?

 

Right now if I do a normal order it comes out like this:

 

EK1023

T15

T18

T2

T24

T4

T7

 

I want it like this:

 

EK1023

T2

T4

T7

T15

T18

 

What's the magic query?

[requinix]

Any way you can store the prefix and number separately? Must you sort it in MySQL or can you do it in PHP?

[fenway]

SUBSTRING_INDEX() is your friend.

[silkfire]

I solved it by making 2 columns and letting PHP Regex split the letter and the number part. fenway the letter part is not  of fixed width so it wouldn't work.

[fenway]

I solved it by making 2 columns and letting PHP Regex split the letter and the number part. fenway the letter part is not  of fixed width so it wouldn't work.

There is still a way to do this with locate, but two columns is better anyway.