SQL Server 2008中的字母数字排序

Question

有人能帮我如何对这个进行排序吗？
我有一个动态模式的列表需要排序，其中包含字母和数字。

CREATE TABLE dbo.Pattern (Pattern varchar(50) NULL)
INSERT INTO dbo.Pattern (Pattern) VALUES ('A11')
INSERT INTO dbo.Pattern (Pattern) VALUES ('A12')
INSERT INTO dbo.Pattern (Pattern) VALUES ('A8')
INSERT INTO dbo.Pattern (Pattern) VALUES ('A2')
INSERT INTO dbo.Pattern (Pattern) VALUES ('B6')
INSERT INTO dbo.Pattern (Pattern) VALUES ('B21')
INSERT INTO dbo.Pattern (Pattern) VALUES ('B10')
INSERT INTO dbo.Pattern (Pattern) VALUES ('B3')
INSERT INTO dbo.Pattern (Pattern) VALUES ('B100')
INSERT INTO dbo.Pattern (Pattern) VALUES ('B2')
INSERT INTO dbo.Pattern (Pattern) VALUES ('AA')
INSERT INTO dbo.Pattern (Pattern) VALUES ('BA')
INSERT INTO dbo.Pattern (Pattern) VALUES ('A20')
INSERT INTO dbo.Pattern (Pattern) VALUES ('AB')
INSERT INTO dbo.Pattern (Pattern) VALUES ('BB')

SELECT Pattern FROM dbo.Pattern ORDER BY Pattern

DROP Table dbo.Pattern

结果如下所示：

 A11
 A12
 A2
 A20
 A8
 AA
 AB
 B10
 B100
 B2
 B21
 B3
 B6
 BA
 BB

但是我想展示的只是像这个结果一样：

AA
A1
A2
A8
A11
A12
A20
AB
BA
B2
B3
B6
B10
B21
B100
BB

- itsMacyAnn

---

加1作为示例数据 - TheGameiswar

你能定义一下你所期望结果的逻辑吗？为什么AA会排在B100之后？在AAA和BA1之间，哪个会先出现？为什么？如果你能定义这个逻辑，我相信你很容易就能回答自己的问题了。 - Tab Alleman

@TabAlleman，我已经编辑了应该输出模式排序结果的内容。 - itsMacyAnn

Answer 1

对于您的样本数据，这个接近：

order by left(pattern, patindex('%[0-9]%', pattern)),
         patindex('%[0-9]%', pattern),
         len(pattern) asc,
         pattern

但是，您希望所有字母都排在最后，因此需要使用 case（我认为）：

order by left(pattern, patindex('%[0-9]%', pattern)),
         (case when pattern like '%[0-9]%'
               then patindex('%[0-9]%', pattern)
               else 999
          end),
         len(pattern) asc,
         pattern

- Gordon Linoff

---

为什么它没有按照上述要求返回输出？ - Shushil Bohara

这个逻辑意味着B1会排在A10之前，因为它的长度较短。 - Tab Alleman

@Suraz...你已经有答案了，但我还是修正了这个答案。 - Gordon Linoff

Answer 2

SELECT Pattern
FROM dbo.Pattern
ORDER BY CASE WHEN PATINDEX('%[0-9]%', Pattern) > 0
              THEN LEFT(Pattern, PATINDEX('%[0-9]%', Pattern)-1)
              ELSE Pattern
         END,
         CASE WHEN PATINDEX('%[0-9]%', Pattern) > 0
              THEN CONVERT(INT, SUBSTRING(Pattern, PATINDEX('%[0-9]%', Pattern), LEN(Pattern)))
              ELSE 0
         END

请在发帖之前搜索网络和Stack Overflow。

参考资料：http://www.essentialsql.com/use-sql-server-to-sort-alphanumeric-values/

- Tim Biegeleisen

---

它返回“传递给LEFT或SUBSTRING函数的无效长度参数”错误。对于AA、BA的值抛出错误，因为它们没有数值，所以LEFT('AA', 0 - 1)会抛出错误。 - Arulkumar

无效的长度参数传递给LEFT或SUBSTRING函数。 - itsMacyAnn

@ayakamacy，我更新了我的答案，使用CASE表达式来处理仅包含字母的模式。 - Tim Biegeleisen

@Arulkumar，我更新了我的答案，麻烦您看一下。 - Tim Biegeleisen

Answer 3

SELECT Pattern
FROM dbo.Pattern 
ORDER BY LEFT(Pattern,1), 
        CASE WHEN SUBSTRING(Pattern,2,LEN(Pattern)) LIKE '%[0-9]%' THEN CAST(SUBSTRING(Pattern,2,LEN(Pattern)) as int) 
            WHEN SUBSTRING(Pattern,2,LEN(Pattern)) = 'A' THEN 0
            ELSE 10000000 END,
            SUBSTRING(Pattern,2,LEN(Pattern))

将输出：

Pattern
AA
A2
A8
A11
A12
A20
AB
BA
B2
B3
B6
B10
B21
B100
BB

- gofr1

Answer 4

我想要分离字母和数字部分：

ORDER BY
    CASE WHEN PATINDEX('%[0-9]%', Pattern)=0 THEN 1 ELSE 0 END,--Put no-nums last
    CASE WHEN PATINDEX('%[0-9]%', Pattern) != 0 THEN LEFT(Pattern, PATINDEX('%[0-9]%', Pattern)-1) ELSE Pattern END,
    CASE WHEN PATINDEX('%[0-9]%', Pattern) != 0 THEN SUBSTRING(Pattern, PATINDEX('%[0-9]%', Pattern), LEN(Pattern)) END

- Paweł Dyl

Answer 5

使用CROSS APPLY简化行内计算。

select pattern
from pattern
cross apply (
    select leftLen = isnull(nullif(patindex('%[0-9]%', pattern),0) - 1, len(pattern))
          ,totalLen = len(pattern)  
    ) c
order by 
 case leftLen when totalLen then 2 else 1 end,
 left(Pattern, leftLen),
 cast(right(Pattern, totalLen-leftLen) as int)

- Serg