Convert text into number in MySQL query

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.

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

 Answers

This should work:

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

---

You can use CAST() to convert from string to int. e.g. SELECT CAST('123' AS INTEGER);

---

Simply use CAST,

CAST(column_name AS UNSIGNED)

The type for the cast result can be one of the following values:

BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

---

if your primary key is a string in a format like 

ABC/EFG/EE/13/123(sequence number)
this sort of string can be easily used for sorting with the delimiter("/")

we can use the following query to order a table with this type of key

SELECT * FROM `TABLE_NAME` ORDER BY 
CONVERT(REVERSE(SUBSTRING(REVERSE(`key_column_name`), 1, LOCATE('/', REVERSE(`key_column_name`)) - 1)) , UNSIGNED INTEGER) DESC

---

A generic way to do :

SELECT * FROM your_table ORDER BY LENTH(your_column) ASC, your_column ASC