Sort varchar column in mySql

Question

I am trying to sort one column having values

FMOL1001,
FMOL1004,
FMOL1009,
FMOL10010,
FMOL1003,
FMOL10025

But it is not sorting properly,please help

Answer 1

Sample data :

id  name
-------------
1   FMOL1001
2   FMOL1004 
3   FMOL1009 
4   FMOL10010
5   FMOL1003 
6   FMOL10025

Query :

SELECT id, name
FROM table
ORDER BY LPAD(SUBSTR(name,5,LENGTH(name)-4),10,'0') ASC

Output :

id  name
-------------
1   FMOL1001
5   FMOL1003 
2   FMOL1004 
3   FMOL1009 
4   FMOL10010
6   FMOL10025

Explanation :

LPAD(SUBSTR(name,5,LENGTH(name)-4),10,'0')

Will produce :

FMOL0000001001
FMOL0000001003 
FMOL0000001004 
FMOL0000001009 
FMOL0000010010
FMOL0000010025

Answer 2

Try this, but im not sure... If you made a numeric string like this FMOLXXXX-->>XXX then you can sort XXX:

SELECT column, SUBSTRING(column FROM 5) sort FROM table ORDER by CAST(sort AS UNSIGNED)