如何按数字顺序排序SQL Server中的字母数字混合NVARCHAR列？

Question

我有以下SQL语句:

SELECT fldTitle 
FROM tblTrafficAlerts 
ORDER BY fldTitle

以下是返回结果（来自NVARCHAR列）的顺序：

A1M northbound within J17 Congestion
M1 J19 southbound exit Congestion
M1 southbound between J2 and J1 Congestion
M23 northbound between J8 and J7 Congestion
M25 anti-clockwise between J13 and J12 Congestion
M25 clockwise between J8 and J9 Broken down vehicle
M3 eastbound at the Fleet services between J5 and J4A Congestion
M4 J19 westbound exit Congestion

您会发现M23和M25列在M3和M4的上面，这看起来不太顺眼，如果要扫描更长的结果列表，您不希望按照这个顺序阅读它们。

因此，我希望结果能按字母顺序排序，然后按数字顺序排列，看起来像这样：

A1M northbound within J17 Congestion
M1 J19 southbound exit Congestion
M1 southbound between J2 and J1 Congestion
M3 eastbound at the Fleet services between J5 and J4A Congestion
M4 J19 westbound exit Congestion
M23 northbound between J8 and J7 Congestion
M25 anti-clockwise between J13 and J12 Congestion
M25 clockwise between J8 and J9 Broken down vehicle

所以M3和M4出现在M23和M25之上。

- Rich

---

使用的数据库管理系统标签。(答案可能取决于所使用的产品。) - jarlh

微软 SQL Server 2008 - 感谢标签编辑建议！ - Rich

@JaydipJ，楼主并没有说它的工作结果不符合预期。他只是在问如何与默认排序方式不同地进行排序。 - Po-ta-toe

@CarlSixsmith 是的，没错。我也是这么说的。 - Jaydip Jadhav

MS SQL Server有任何数字敏感的排序规则吗？ - jarlh

相关/重复：https://dev59.com/NGQn5IYBdhLWcg3wpomp请仅获取字符串中的数字。 - JimmyB

Answer 1

这应该可以解决问题。同时添加了一些奇怪的数据以确保排序也适用于它：

SELECT x
FROM 
(values
('A1M northbound within J17 Congestion'),
('M1 J19 southbound exit Congestion'),
('M1 southbound between J2 and J1 Congestion'),
('M23 northbound between J8 and J7 Congestion'),
('M25 anti-clockwise between J13 and J12 Congestion'),
('M25 clockwise between J8 and J9 Broken down vehicle'),
('M3 eastbound at the Fleet services between J5 and J4A Congestion'),
('M4 J19 westbound exit Congestion'),('x'), ('2'), ('x2')) x(x)
ORDER BY
  LEFT(x, patindex('%_[0-9]%', x +'0')), 
  0 + STUFF(LEFT(x, 
  PATINDEX('%[0-9][^0-9]%', x + 'x1x')),1,
  PATINDEX('%_[0-9]%', x + '0'),'')

结果：

2
A1M northbound within J17 Congestion
M1 J19 southbound exit Congestion
M1 southbound between J2 and J1 Congestion
M3 eastbound at the Fleet services between J5 and J4A Congestion
M4 J19 westbound exit Congestion
M23 northbound between J8 and J7 Congestion
M25 anti-clockwise between J13 and J12 Congestion
M25 clockwise between J8 and J9 Broken down vehicle
x
x2

- t-clausen.dk

---

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - Rich

Answer 2

也许这不太美观，但它确实能工作：

DECLARE @tblTrafficAlerts  TABLE
(
    fldTitle NVARCHAR(500)
);

INSERT INTO @tblTrafficAlerts  (fldTitle)
VALUES (N'A1M northbound within J17 Congestion')
    , (N'M1 J19 southbound exit Congestion')
    , (N'M1 southbound between J2 and J1 Congestion')
    , (N'M23 northbound between J8 and J7 Congestion')
    , (N'M25 anti-clockwise between J13 and J12 Congestion')
    , (N'M25 clockwise between J8 and J9 Broken down vehicle')
    , (N'M3 eastbound at the Fleet services between J5 and J4A Congestion')
    , (N'M4 J19 westbound exit Congestion');

SELECT *
FROM @tblTrafficAlerts AS T
CROSS APPLY (SELECT PATINDEX('%[0-9]%', T.fldTitle)) AS N(NumIndex)
CROSS APPLY (SELECT PATINDEX('%[0-9][^0-9]%', T.fldTitle)) AS NN(NextLetter)
ORDER BY SUBSTRING(T.fldTitle, 0, N.NumIndex), CONVERT(INT, SUBSTRING(T.fldTitle, N.NumIndex, NN.NextLetter - 1));

这将提取第一个数字之前的所有内容，按照它进行排序，然后提取该数字并将其作为整数进行排序。

输出如下：

╔══════════════════════════════════════════════════════════════════╗
║                             fldTitle                             ║
╠══════════════════════════════════════════════════════════════════╣
║ A1M northbound within J17 Congestion                             ║
║ M1 J19 southbound exit Congestion                                ║
║ M1 southbound between J2 and J1 Congestion                       ║
║ M3 eastbound at the Fleet services between J5 and J4A Congestion ║
║ M4 J19 westbound exit Congestion                                 ║
║ M23 northbound between J8 and J7 Congestion                      ║
║ M25 anti-clockwise between J13 and J12 Congestion                ║
║ M25 clockwise between J8 and J9 Broken down vehicle              ║
╚══════════════════════════════════════════════════════════════════╝

- Evaldas Buinauskas

Answer 3

SELECT fldTitle FROM tblTrafficAlerts order by LEFT(fldTitle , CHARINDEX(' ', fldTitle) - 1), fldTitle 

或者使用patindex

ORDER BY LEFT(Col1,PATINDEX('%[^0-9]%',Col1)-1)

- Anil

Answer 4

我会这样做：

编辑：我将其分为两部分：首字母和第二部分。这使您可以（如果需要）在数字上处理第二部分（但第一行中有一个令人不安的“M”...）

只做第二步会更容易：在第一个空格处切割，检查长度并在排序时添加“0”（如果需要）。

DECLARE @tblTrafficAlerts TABLE(fldTitle VARCHAR(500));

INSERT INTO @tblTrafficAlerts VALUES 
 ('A1M northbound within J17 Congestion')
,('M1 J19 southbound exit Congestion')
,('M1 southbound between J2 and J1 Congestion')
,('M23 northbound between J8 and J7 Congestion')
,('M25 anti-clockwise between J13 and J12 Congestion')
,('M25 clockwise between J8 and J9 Broken down vehicle')
,('M3 eastbound at the Fleet services between J5 and J4A Congestion')
,('M4 J19 westbound exit Congestion');

SELECT ta.fldTitle
      ,Leading.Letter
      ,Leading.SecondPart
FROM @tblTrafficAlerts AS ta
CROSS APPLY(SELECT SUBSTRING(ta.fldTitle,1,1) AS Letter
                  ,SUBSTRING(ta.fldTitle,2,CHARINDEX(' ',ta.fldTitle)-1) AS SecondPart) AS Leading
ORDER BY Leading.Letter,CASE WHEN LEN(Leading.SecondPart)=1 THEN Leading.SecondPart + '0' ELSE Leading.SecondPart END

结果：

fldTitle                                                           Letter   SecondPart
A1M northbound within J17 Congestion                               A        1M 
M1 J19 southbound exit Congestion                                  M        1 
M1 southbound between J2 and J1 Congestion                         M        1 
M23 northbound between J8 and J7 Congestion                        M        23 
M25 anti-clockwise between J13 and J12 Congestion                  M        25 
M25 clockwise between J8 and J9 Broken down vehicle                M        25 
M3 eastbound at the Fleet services between J5 and J4A Congestion   M        3 
M4 J19 westbound exit Congestion                                   M        4 

- Shnugo