Help with T-sql special sorting rules

Question

I have a field like：

SELECT * FROM 
(
    SELECT 'A9t' AS sortField UNION ALL 
    SELECT 'A10t' UNION ALL 
    SELECT 'A11t' UNION ALL 
    SELECT 'AB9F' UNION ALL 
    SELECT 'AB10t' UNION ALL        
    SELECT 'AB11t'  
) t ORDER BY sortField

and the result is:

sortField
---------
A10t
A11t
A9t
AB10t
AB11t
AB9F

Actually I need is to combine the string and number sorting rules:

sortField
---------
A9t
A10t
A11t
AB9F
AB10t
AB11t

Answer 1

If the first character is always a letter, try:

SELECT * FROM 
(
    SELECT 'A9t' AS sortField UNION ALL 
    SELECT 'A10t' UNION ALL 
    SELECT 'A11t'
) t ORDER BY substring(sortField,2,len(sortField)-1) desc

Answer 2

SELECT   *
FROM     ( 
            SELECT 'A9t' AS sortField UNION ALL 
            SELECT 'A10t' UNION ALL 
            SELECT 'A11t' UNION ALL 
            SELECT 'AB9F' UNION ALL 
            SELECT 'AB10t' UNION ALL        
            SELECT 'AB11t' 
         )
         t
ORDER BY LEFT(sortField,PATINDEX('%[0-9]%',sortField)-1)                                                                                       ,
         CAST(substring(sortField,PATINDEX('%[0-9]%',sortField),1 + PATINDEX('%[0-9][A-Z]%',sortField) -PATINDEX('%[0-9]%',sortField) ) AS INT),
         substring(sortField,PATINDEX('%[0-9][A-Z]%',sortField)   + 1,LEN(sortField))

Answer 3

I would say that you have combined the alpha and numeric sort. But what I think you're asking is that you want to sort letters in ascending order and numbers in descending order, and that might be hard to do in a nice looking way. The previous answers will not working for your problem, the problem is that Martin Smith's solution doesn't take strings with two letters as prefix and Parkyprg doesn't sort numbers before letters as you ask for.

What you need to do is to use a custom order, see example here: http://www.emadibrahim.com/2007/05/25/custom-sort-order-in-a-sql-statement/, but that is a tedious way to do it.

EDIT: Martins Smith's solution is updated and works just fine!