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
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
This is because you're trying to sort strings, not numbers.
One way out of this is to make a separate int column so it will be a bit faster on the sorting.
The other way is this one:
SELECT * FROM `table` ORDER BY CAST(SUBSTRING(column,LOCATE('-',column)+1) AS SIGNED)
Personally I prefer to use a separate column, so you don't really rely on the names format.
An alternative way of doing this is to sort by the length and then the field:
order by char_length(col), col