Search code examples
mysqlsortingnumericvarchar

Sorting varchar column as numeric in mysql with null value as the bottom

i am trying to sort a column in the mysql query which is of type varchar but has may/maynot have numeric values.

for example it may have the values

2012-10
2012-41
2012-1

which should be sorted as follows:
2012-1
2012-10
2012-41

but if the values are :
M-1
M-13
M-5

it should be sorted as :
M-1
M-5
M-13

and if null values are present it should be last.

Dont know if it is possible. Please Help

Solution

  • If you want a numeric sort by the number after the hyphen, use this:

    ORDER BY column IS NOT NULL, SUBSTRING_INDEX(column, '-', 1), CAST(SUBSTRING_INDEX(column, '-', 2) AS DECIMAL)

    column IS NOT NULL sorts the null columns last, the CAST() expression sorts the rest by the number.