SQL sorting problem

Walle

Have problem when there is letter and number in same column, in title etc. Example when using ORDER BY column asc (a-z) it sorting like this

A 32
A 8
B 32
B 8

mysql think 32 is less than 8, or 32 as 3 which is less than 8 and then correct sorting by SQL, but I want sort like this

A 8
A 32
B 8
B 32

Is it possible to fix this somehow? Is there some special data-type setting for column and contents like this?

laserlight

You can fix it in PHP by using [man]natsort/man, but there may be no equivalent in the MySQL dialect of SQL.

If the preceding letters are of the same length, then you can sort according to length and then according to the column.

Walle

Thanks for your tip. I guess that’s not possible with only sql than. natsort()looks advanced and guess I need time to read more.

It’s not really important sort like that in this case and more or less just wondered if it was possible some simple way with sql. Good to know it’s not possible.

Thanks for help

johanafm

You could split your values into two columns, keeping letters in one and numbers in the other, then simply: ORDER BY letter ASC, number ASC.

Walle

I know but this is if letter and number is mixed. In title/heading. Example if need to filter this forum where there is thread named "IIS 8 error" and another named "IIS 32 error", and want sort title as less error it will sort like this with asc (a-z/0-9).

IIS 32 error
IIS 8 error

Should be instead like

IIS 8 error
IIS 32 error

I thought there maybe was any data-type in sql to use for column that store this kind of mixed data. Its no bigg problem really. I guess SQL sort correct in this case and comparing first a-z and 0-9 characters it finds, in this case only compare 3 with 8. Which 3 is less than 8 and correct sorting by SQL.