按varchar字母顺序对数据进行排序

古塔姆

我有下面的数据表。

 1
 AAAAA01 
 AAAAA01
 AAAAA01
 B21
 AAAAAA1
 B3
 AB100

我想按以下顺序对数据进行排序

 AAAAAA1
 AAAAA01
 AAAAA01
 AAAAA01
 AB100
 B21
 B3
 1

我写了一条声明，但没有给我正确的结果。

    Select
    *
from
    dbo.Section
order by      
    CASE    
      WHEN not Section like '%[^0-9]%' THEN CONVERT(int,Section)
      WHEN  Section like '[0-9]%' THEN CONVERT(int,SUBSTRING(Section,1,PATINDEX('%[A-Z]%',Section)-900000))
    END

为您的帮助，我提供表格脚本

  INSERT [dbo].[Section] ([Section]) VALUES (N'1')
INSERT [dbo].[Section] ([Section]) VALUES (N'AAAAA01')
INSERT [dbo].[Section] ([Section]) VALUES (N'AAAAA01')
INSERT [dbo].[Section] ([Section]) VALUES (N'AAAAA01')
INSERT [dbo].[Section] ([Section]) VALUES (N'AAAAA01')
INSERT [dbo].[Section] ([Section]) VALUES (N'AAAAA01')
INSERT [dbo].[Section] ([Section]) VALUES (N'B21')
INSERT [dbo].[Section] ([Section]) VALUES (N'AAAAAA1')
INSERT [dbo].[Section] ([Section]) VALUES (N'B3')
INSERT [dbo].[Section] ([Section]) VALUES (N'AB100')
INSERT [dbo].[Section] ([Section]) VALUES (N'2')
INSERT [dbo].[Section] ([Section]) VALUES (N'B1')
INSERT [dbo].[Section] ([Section]) VALUES (N'B32')
INSERT [dbo].[Section] ([Section]) VALUES (N'11')
INSERT [dbo].[Section] ([Section]) VALUES (N'A10')
INSERT [dbo].[Section] ([Section]) VALUES (N'ABAAAA')

您能告诉我如何为我提供正确答案。

斯蒂芬·诺维科夫（Stepan Novikov）

请试试：

Select    
    *
from
    dbo.Section
order by
    REPLACE
    (REPLACE
    (REPLACE
    (REPLACE
    (REPLACE
    (REPLACE
    (REPLACE
    (REPLACE
    (REPLACE
    (REPLACE ([Section], '0', 'ZZ0'),
    '1', 'ZZ1'),
    '2', 'ZZ2'),
    '3', 'ZZ3'),
    '4', 'ZZ4'),
    '5', 'ZZ5'),
    '6', 'ZZ6'),
    '7', 'ZZ7'),
    '8', 'ZZ8'),
    '9', 'ZZ9'),
    [Section]

Sqlfiddle

选择新测试数据的结果：