Search code examples
mysql

sorting alphanumeric in mysql?

040, 044P, 041BL, 041W, 041PB

^^ This is the order it is coming out in by using Order By clause.

I think this is how it should appear instead: 

040, 041BL, 041PB, 041W, 044P

I know similar questions must have been asked before, but I still can't figure out anything!

Edit: After X.L.Ant's comment, I realized my mistake. Therefore, simple order by clause is working for the test case given above. However, the case is still complicated if the number of digits is not always going to be 3 as GolezTrol mentioned. What should one do in that case?

Solution

  • Try:

    SELECT string,
       @num := CONVERT(string, signed)                            AS num_part,
       Substring(Trim(LEADING '0' FROM string), Length(@num) + 1) AS rest_of_string
FROM   table1
ORDER  BY num_part,
          rest_of_string

    This way, the numbers will still be ordered by their numerical value (the leading 0s not being taken into account).

    See fiddle.