How to do natural sort for letter number?-CodePudding

How would I do a natural sort of a column that has c then number so example

It would sort like least to greatest or greatest to least

CodePudding user response：

We can try sorting on the digit component of the string, e.g.

SELECT *
FROM yourTable
ORDER BY CAST(RIGHT(col, LEN(col) - 1) AS INT);

Note that the cast to integer in the ORDER BY clause is very necessary assuming you want to sort numerically rather than lexicographically (as text).

CodePudding user response：

Would using the below work for you?

SELECT FOO FROM BAR
WHERE X IN Y
ORDER BY RIGHT(FOO,LEN(FOO)-1) DESC

Please note this is untested.