Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a column that has strings of the following type:
Miller 10
Allen 20
King 10
....
Jones 100

I try to sort the column based on the numerical part of the data.
I tried the following:

SELECT * FROM the_table ORDER BY CAST(RIGHT(data,2) AS UNSIGNED);

But this does not work. It places Jones 100 as the first entry etc.

What am I doing wrong?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

Look for the space, then cast everything after the space

SELECT * FROM the_table
ORDER BY CAST(SUBSTR(data,LOCATE(' ',data)+1) AS UNSIGNED);

Give it a Try !!!

Your query did not work because the RIGHT 2 of 100 is 00, which would be numerically first.

share|improve this answer
    
+1:Yes good point on 100. –  Cratylus Apr 15 '13 at 19:54
    
Do you accept my answer ??? –  RolandoMySQLDBA Apr 15 '13 at 20:19
    
Thank You, kind sir !!! –  RolandoMySQLDBA Apr 15 '13 at 20:21
    
Thank you for your help! –  Cratylus Apr 15 '13 at 20:22

MySQL has a built-in function for parsing texts in the form of SUBSTRING_INDEX().

This simplifies your query to:

SELECT * FROM the_table
ORDER BY CAST(SUBSTRING_INDEX(data,' ',-1) AS UNSIGNED);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.