Search code examples
sqlmysqlnatural-sort

Ordering a varchar column in MySQL in an Excel-like manner

I have a varchar column with mixed data- strings, integers, decimals, blank strings, and null values. I'd like to sort the column the same way that Excel would, first sorting numbers and then sorting the strings. For example:

  • 1
  • 2
  • 3
  • 3.5
  • 10
  • 11
  • 12
  • alan
  • bob
  • carl
  • (blank/null)
  • (blank/null)

I've tried doing something like 'ORDER BY my_column+0' which sorts the numbers correctly but not the strings. I was hoping someone might know of an efficient way to accomplish this.

MartinofD's suggestion works for the most part and if I expand on it a little bit I can get exactly what I want:

SELECT a FROM test ORDER BY a IS NULL OR a='', a<>'0' AND a=0, a+0, a;

Pretty ugly though and I'm not sure if there are any better options.

Solution

  • This works:

    SELECT a FROM test ORDER BY a IS NULL OR a='', a<>'0' AND a=0, a+0, a;

    Any more efficient/elegant solution would be welcome however.