Need a help for sort in mysql

mysql, sql-order-by

Solution

This would do your required sort order, even in the presence of `0` in the table;

SELECT * FROM t 
ORDER BY 
  st REGEXP '^[[:alpha:]].*', 
  st+0, 
  st

An SQLfiddle to test with.

- As a first sort criteria, it sorts anything that starts with a letter after anything that doesn't. That's what the regexp does.

- As a second sort criteria it sorts by the numerical value the string starts with (`st+0` adds 0 to the numerical part the string starts with and returns an int)

- As a last resort, it sorts by the string itself to get the alphabetical ones in order.

Problem

Hi I want to sort a table .The field contains numbers,alphabets and numbers with alphabets ie, ``` 1 2 1a 11a a 6a b ``` I want to sort this to, ``` 1 1a 2 6a 11a a b ``` My code is, ``` SELECT * FROM t ORDER BY CAST(st AS SIGNED), st ``` But the result is, ``` a b 1 1a 2 6a 11a ``` I found this code in this url ``` "http://www.mpopp.net/2006/06/sorting-of-numeric-values-mixed-with-alphanumeric-values/" ``` Anyone please help me

Original source