mysql查询出的数据转换为数字,在MySQL查询中将文本转换为数字

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?

解决方案

This should work:

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

FROM table

ORDER BY num;