Convert partially non-numeric text into number in MySQL query

Question

This should work:

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

You can use SUBSTRING and CONVERT:

SELECT stuff
FROM table
WHERE conditions
ORDER BY CONVERT(SUBSTRING(name_column, 6), SIGNED INTEGER);

Where name_column is the column with the "name-" values. The SUBSTRING removes everything up before the sixth character (i.e. the "name-" prefix) and then the CONVERT converts the left over to a real integer.

UPDATE: Given the changing circumstances in the comments (i.e. the prefix can be anything), you'll have to throw a LOCATE in the mix:

ORDER BY CONVERT(SUBSTRING(name_column, LOCATE('-', name_column) + 1), SIGNED INTEGER);

This of course assumes that the non-numeric prefix doesn't have any hyphens in it but the relevant comment says that:

name can be any sequence of letters

so that should be a safe assumption.

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]

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