Alphanumeric sorting/ordering from database

Question

in database, it store values of A1, A2, A10, A12, A5, A8 but when i retrieve it out it show as below:-

A1, A10, A12, A2, A5, A8

is there a way to sort it correctly? like

A1, A2, A5, A8, A10, A12

Answer 1

If the string always starts with a single alpha/numeric prefix then you can use

 SELECT no from my_table order by substring(no,1,1), substring(no,2)+0 

fiddle here

In PHP you can sort once it's in a multidimensional array by using usort returning the results of a strnatcasecmp call on the fields.

usort($records, function($a, $b){return strnatcasecmp($a["fieldname"], $b["fieldname"]);});

Answer 2

This is known as a natural sort, or a natural order.

MySQL doesn't have the built-in ability to do a natural sort.
There's various options listed in response to this question: Natural Sort in MySQL ...but nothing that easily does what you want.

What you can do though, is get all the data back to PHP, and do the sorting there using the natsort() function.

$vals = array('A1', 'A10', 'A12', 'A2', 'A5', 'A8');
natsort($vals);
print_r($vals); // A1, A2, A5, A8, A10, A12

This just sorts single values - but if you need to sort entire rows, you get the same functionality by using usort() with the strnatcmp() function

Note that this requires you to fetch all the rows in order to sort them - any limiting or paging must be done after the sort in PHP, which can quickly become inefficient with large datasets.

Answer 3

if the prefixes are always similar, you could maybe order by length first?

ORDER BY LENGTH(column), column

Answer 4

Array
(
    [1] => Array
        (
            [thumb] => oiuy
        )

    [1a] => Array
        (
            [thumb] => asdf
        )

    [1b] => Array
        (
            [thumb] => 2345
        )
)

uksort($array, function($a, $b) {

    sscanf($a, '%d%s', $an, $as);
    sscanf($b, '%d%s', $bn, $bs);

    if($an == $bn)
        return strcmp($as, $bs);

    return $an - $bn;
});

Answer 5

Use natsort(). This works perfect with alphanumeric values.