Mysql natural sorting
by Jayanthi[ Edit ] 2013-07-30 16:11:59
Mysql natural sorting
Sorting string column in mysql
Example 1:
The name field is alphanumeric. like that
m10,m1,m20,m2,m14.we have normally sorted by name produce result is
m1,m10,m14,m2,m20.
But to get order
m1,m2,m10,m14,m20.
SELECT id,name,status,CONVERT(SUBSTRING(name FROM 3),UNSIGNED INTEGER) part
ORDER BY part;
Example 2:
Sometimes, we sort first 5 characters in column.Use below query.If you change "FROM number". When the number changed.Number of character take for sorting starts with first character.
SELECT * FROM product where category='linux' and status='active' ORDER BY
CAST(SUBSTR(name FROM 5) AS UNSIGNED)
How to length for sorting given to this query.
