CODEWITHSUNDEEP
mysqlsorting
Aadi
Aadi

Reputation: 7109

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

Upvotes: 1

Views: 136

Answers (2)

1000111
1000111

Reputation: 13519

In order to get the first preferred order: (Number first then letters)

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

In order to get the alternative preferred order: (Letter first then number)

SELECT 
*
FROM YOUR_TABLE
ORDER BY CAST(player_number AS UNSIGNED),player_number

Upvotes: 2

Blank
Blank

Reputation: 12378

Try following;)

select player_number
from table1
order by if(player_number != '0' and (player_number + 1) = 1, 0, (player_number + 1)), player_number

SQLFiddle DEMO HERE

Upvotes: 1

Related Questions