Convert text into number in MySQL query

Question

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

name-1

name-11

name-12

name-2

If I cut off the number, can I convert the 'varchar' number into the 'real' number and use it to sort the rows? I would like to obtain the 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 the following solution ... ORDER BY column * 1. If the name will not contain any numbers is it save to use that solution?

Answer 1

Use the below query:

SELECT field,CONVERT(SUBSTRING_INDEX(field,'-',-1),UNSIGNED INTEGER) AS num

FROM table

ORDER BY num;