Mysql顺序排序，排序不正常

Question

I have table with names as sth-1, sth-2, .........sth-10, sth-11 and so on.

I want to sort them serially but it fetches as 1, 11, 12, .., 2,3 and so on when sorted by name in asc order.

Any help appreciated

Answer 1

This should work for you if the numbers are always going to follow the -

SELECT   *
FROM     table
ORDER BY CAST(SUBSTRING(column,LOCATE('-',column)+1) AS SIGNED)

This original query is from

Sorting string column containing numbers in SQL?

Adopted to your needs