Convert text into number in MySQL query

Question

Is it possible to convert text into number within MySQL query? I have a column with an identifier that consists a name and a number in the format of "name-number". The column has VARCHAR type. I want to sort the rows according the number (rows with the same name) but the column is sorted according do character order, i.e.

name-1
name-11
name-12
name-2

If I cut of the number, can I convert the 'varchar' number into the 'real' number and use it to sort the rows? I would like to obtained following order.

name-1
name-2
name-11
name-12

I cannot represent the number as a separate column.

edited 2011-05-11 9:32

I have found following solution ... ORDER BY column * 1. If the name will not contain any numbers is it save to use that solution?

Answer 1

This should work:

SELECT field,CONVERT(SUBSTRING_INDEX(field,'-',-1),UNSIGNED INTEGER) AS num
FROM table
ORDER BY num;