Mysql natural sorting

by Jayanthi 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.

814
like
0
dislike
0
mail
flag

You must LOGIN to add comments