SQL ordering by text ASC, show numbers at last position

Question

I have a SQL query and I need to order the result in ASC, but I need the numbers and spaces to be in the last position of the result, not first.

SELECT title FROM movie ORDER BY title ASC

Using the code above I get the titles starting from [space], 0-9, A,B,C... I need this form: A,B,C,...,Z,[anything else]

Thanks for any help.

Answer 1

ORDER BY CASE
           WHEN LOWER(LEFT(title, 1)) BETWEEN 'a' AND 'z' THEN 0
           ELSE 1
         END,
         title

Answer 2

This is a bit of a hack, but it should work.

SELECT title FROM movie 
ORDER BY CASE WHEN title LIKE '[0-9]%' THEN 'ZZZ' + title ELSE title END ASC

The idea is to make your order clause transform the items starting with numbers by prepending them with ZZZ. As for the items starting with a space, you may just want to clean them up with LTRIM.