Need a help for sort in mysql

Question

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

Answer 1

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.

Answer 2

You can use this:

SELECT *
FROM t
ORDER BY
  st+0=0, st+0, st

Using st+0 the varchar column will be casted to int. Ordering by st+0=0 will put alphanumeric rows at the bottom (st+0=0 will be 1 if the string starts with an alphanumeric character, oterwise it will be 0)

Please see fiddle here.