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

I think it's called "natural order". There's already the same (?) question [here](http://stackoverflow.com/questions/34509/natural-human-alpha-numeric-sort-in-microsoft-sql-2005). — pascal, Sep 27 '10 at 10:39

Martin Smith · Answer 1 · 2010-09-27T09:49:48.213

4

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))

edited Sep 27 '10 at 09:49

answered Sep 27 '10 at 09:31

Martin Smith

438,706
87
741
845

Thank you,in your solution the string of field is same rule,I changed the subject,please have a look – guaike Sep 27 '10 at 09:41
Edited to deal with prefixes and suffixes > 1 character. – Martin Smith Sep 27 '10 at 09:53

score 1 · Answer 2 · answered Sep 27 '10 at 09:30

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

answered Sep 27 '10 at 09:30

CristiC

22,068
12
57
89

Tomas Jansson · Answer 3 · 2010-09-27T09:59:37.863

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!

yes it does, nice fix during my post. you would get my vote if I could vote. — Tomas Jansson, Sep 27 '10 at 09:58

Help with T-sql special sorting rules

3 Answers3