abhis abhis - 5 months ago 14
MySQL Question

Mysql order by in a char datatype with combination of numbers and characters

In a mysql table field "player_number" accept 3 char - It may accept numbers only, alphabets only or a combination of both with maximum 3 letters.
Table have entry like as follows,


  • TR

  • 11

  • 1

  • 2

  • 222

  • A

  • AA



Is there any quick way to display sorted "player_number" result as follows,

Preferred order


  • 1

  • 2

  • 11

  • 222

  • A

  • AA

  • TR



Alternative:


  • A

  • AA

  • TR

  • 1

  • 2

  • 11

  • 222


Answer

Here's the query:

SELECT 
*
FROM YOUR_TABLE
ORDER BY 
CASE WHEN player_number REGEXP '[0-9]+' THEN CAST(player_number AS UNSIGNED) END , col