How can I arrange a varchar field that can contain numbers in alphabetical order?

Question

I am sure this should be a fairly common problem, so I assume that Microsoft has already solved the problem. My Google skills are not just scratched. I have a field that I want to order, this is a varchar field, for example

• Q
• Num 10
• Num 1
• A
• Num 9
• Num 2
• F

Now I expect the result to be

• AND
• F
• Num 1
• Num 2
• Num 9
• Num 10
• Q

But this is not so. This is as follows (note that Num 10 appears after Num 1, not Num 9, as expected)

• AND
• F
• Num 1
• Num 10
• Num 2
• Num 9
• Q

Now I know the reason for this, so you do not need to explain :) But I can’t remember how to solve it, or if there is a good flag or command that I can use to understand this.

EDIT:

. . . - ASCII ?

2: . . -, , . . , , .

Answer 1

Ascii

. http://www.codinghorror.com/blog/archives/001018.html.

Answer 2

, CHARINDEX/SUBSTRING, .

:

select *
from (
    select 'Q' x
    union
    select 'Num 10'
    union
    select 'Num 1'
    union
    select 'A'
    union
    select 'Num 9'
    union
    select 'Num 2'
    union
    select 'F'
) a
order by
    case
        when CHARINDEX(' ', x) <> 0 then LEFT(x, CHARINDEX(' ', x) - 1)
        else x
    end,
    cast(case
        when CHARINDEX(' ', x) <> 0 then substring(x, CHARINDEX(' ', x) + 1, LEN(x) - CHARINDEX(' ', x) )
        else ''
    end as int)

:

A
F
Num 1
Num 2
Num 9
Num 10
Q

Edit:

, . T-SQL, , , , . CLR, . , . .

Answer 3

.

, , , "foo1bar" "foo10bar"? "foo10bar11" "foo10bar1"? "Foo Two" "Foo Three"?

. . " ".

Answer 4

" . , , ."

, - , . .

Essentially, you need to tokenize your sort string into pieces of pure letters and pieces of pure numbers and apply a different sort order for each category. This is doable if you have some kind of template, for example.

   AAA999AA
   A9AAAAA
   A999A

but for each template an individual solution will be required. The general solution for any arbitrary data arrangement is a big question.