sql sort numeric then alphabetically

in this example :

10-20
20-40
50-60

v
k
r
a

12 month
1 month

how can i sort it in this order ?:

10-20
20-40
50-60

a
k
r
v

1 month
12 month

i use abs(value) but in the alphabetical case doesn't work

From stackoverflow

• OK, thanks to the commenter, now a working version. This sorts on two cases in the order by clause:

  select  *
  from    (
          select '10-20' as col1
          union all select '20-40'
          union all select '50-60'
          union all select 'v'
          union all select 'k'
          union all select 'r'
          union all select 'a'
          union all select '12 month'
          union all select '1 month'
          ) s1
  order by
          case
              when col1 rlike '[0-9][0-9]-[0-9][0-9]' then 1
              when col1 rlike '[0-9]+ month' then 3
              else 2
          end
  ,       case
              when col1 rlike '[0-9][0-9]-[0-9][0-9]' then cast(col1 as decimal)
              when col1 rlike '[0-9]+ month' then cast(col1 as decimal)
              else col1
          end
  

  The first case puts categories in order: 00-00 first, then other stuff, and at the end the months. The second case converts the columns to decimal if possible.

  nickf : That puts "2" after "10"

• If you can get away with doing some processing in PHP, you could use natsort:

  Standard sorting
  Array
  (
      [3] => img1.png
      [1] => img10.png
      [0] => img12.png
      [2] => img2.png
  )
  
  Natural order sorting
  Array
  (
      [3] => img1.png
      [2] => img2.png
      [1] => img10.png
      [0] => img12.png
  )
  

  Otherwise, there's another question on SO which asks the same thing: http://stackoverflow.com/questions/153633/natural-sort-in-mysql