如何按字母顺序排序SQL查询，但忽略前导数字？

Question

我无法找到适合我的问题的查询语句。我在数据库中有一个表格，需要按照非常特定的方式对其进行排序 - 我正在排序的列是一个地址，并且它以数字开头，但我需要忽略数字进行排序。

以下是我的数据集：

id  | address
1   | 23 Bridge road
2   | 14 Kennington street
3   | 7  Bridge road
4   | 12 Oxford street
5   | 9  Bridge road

我需要将它排序，如下所示：

id | address
1  |  7 Bridge road
2  |  9 Bridge road 
3  | 23 Bridge road
4  | 14 Kennington street
5  | 12 Oxford street

到目前为止，我只有这个：

SELECT id, address
FROM propertySearch
Order by address ASC. 

有人能帮我解决这个问题吗？

- rsimsek

---

你正在使用什么关系型数据库管理系统？ - Eduard Uta

看一下 SQL 中使用的 TRIM 函数 http://www.w3resource.com/sql/character-functions/trim.php，但要标记您所使用的数据库，因为根据数据库可能会有更好的方法。 - David

地址是如何存储的？通常情况下，您会将门牌号和街道名称存储在不同的列中，但如果没有，我会使用子字符串和修剪来拆分字符串并删除第一个空格之前的所有内容。 - Ryan Thomas

我想你可以使用regexp-replace或其他类似的产品特定函数。但您仍未告诉我们您正在使用哪个dbms。 - jarlh

啊，抱歉。我使用的是 MS SQL。 - rsimsek

Answer 1

如果这个格式始终是这样的（一个前导数字，一个空格，然后是地址），那么你可以采取以下做法：

SQL-Server：

SELECT * FROM YourTable t
ORDER BY SUBSTRING(t.address,CHARINDEX(' ',t.address,1),99)

MySQL :

SELECT * FROM YourTable t
ORDER BY SUBSTRING_INDEX(t.address,' ',-1) 

如果格式不是固定的，你可以使用 SQL-Server 的 patindex() 函数：

SELECT * FROM YourTable t
ORDER BY SUBSTRING(t.address,PATINDEX('%[A-z]%',t.address),99)

注意：这是糟糕的数据库设计!! 每个值应该被妥善地存储在自己的列中，例如 STREET，CITY，APARTMENT_NUMBER 等等，否则，它们会导致正好出现这种情况。

- sagi

---

让我们诚实地说：它不会总是那种格式。 - Twinkles

说实话，为什么不呢？ - sagi

有些地址没有门牌号码。一个例子：女王陛下，白金汉宫，伦敦SW1A 1AA。 - Twinkles

然后 PATINDEX 将返回0，子字符串将从开头开始。 - sagi

不，你的答案会将其归类为“P”而不是“B”。你还有拼写错误的问题，例如“14肯宁顿街”。 - Twinkles

Answer 2

如果您使用SQL Server，可以结合使用PATINDEX和STUFF：

SELECT *, STUFF(T.address, 1, PATINDEX('%[A-z]%', T.address) - 1, '')
FROM #Table1 AS T
ORDER BY STUFF(T.address, 1, PATINDEX('%[A-z]%', T.address) - 1, '')

PATINDEX可以在字符串中找到第一个字母的索引，STUFF用于从开头修剪到该索引的所有内容。

以下是输出:

id  address                 No column name)
---------------------------------------------
1   23 Bridge road          Bridge road
3   7  Bridge road          Bridge road
5   9  Bridge road          Bridge road
2   14 Kennington street    Kennington street
4   12 Oxford street        Oxford street

我还注意到您的期望输出中顺序不同。如果这是有意的，您需要使用ROW_NUMBER函数：

SELECT ROW_NUMBER() OVER(ORDER BY STUFF(T.address, 1, PATINDEX('%[A-z]%', T.address) - 1, ''), T.id) AS ID, T.address
FROM #Table1 AS T;

这个查询将为每一行生成新的ID。
结果：

id  address
------------------------
1   23 Bridge road
2   7  Bridge road
3   9  Bridge road
4   14 Kennington street
5   12 Oxford street

---

无论如何，这是一个比较笨拙的解决方案。

我建议你将地址存储在单独的列中，如街道名称、邮政编码、门牌号码、楼号（可选）、城镇等。这将是一个更好的方法。

- Evaldas Buinauskas

---

如果地址像 25a Bridge Road，可能会出现问题。 - Tedo G.

那其实是正确的。得找到一个解决办法 :) - Evaldas Buinauskas

Answer 3

我认为这种操作更适合于业务层。
如果将所有数据加载到 .net 代码中 - 排序将更加容易，更易读和可维护。

Public Class Address

    Public Property Id As Integer
    Public Property AddressData As String

    'This property can be used for sorting       
    Public ReadOnly Property SortedKey As String
        Get
           Dim rawData As IEnumerable(Of String) = Me.AddressData.Split(" "c).Skip(1)
           Return String.Join(" ", rawData)
        End Get
    End Property

End Class

然后使用LINQ。

Dim loaded As List(Of Address) = yourLoadFunction()
Dim sorted = loaded.OrderBy(Function(item) item.SortedKey).ToList()

- Fabio

Answer 4

根据您标记的vb.net，我猜您使用的是MS SQL。如果您总是使用空格将街道编号和街道名称分开，请尝试按以下方式排序：

ORDER BY RIGHT([address], LEN([address]) - CHARINDEX(' ', [address], 1))

- Tedo G.

---

如果有两个由空格分隔的数字序列，例如 7 5 Bridge Road，它将会失败。 - Evaldas Buinauskas

当然可以，但在正常情况下，街道号码不应该有带空格的数字。 - Tedo G.

你永远不知道用户可能会输入什么。 - Evaldas Buinauskas

是的，输入数据最好在输入时进行验证。 - Tedo G.

Answer 5

Declare @Table table (id int,address varchar(100))
Insert into @Table values
(1,'23 Bridge road'),
(2,'14 Kennington street'),
(3,'7 Bridge road'),
(4,'12 Oxford street'),
(5,'9 Bridge road')

Select * From @Table
 Order By substring(address,patindex('%[a-z]%',address),200)
         ,cast(substring(address,1,charindex(' ',address)) as int)

返回

id  address
3   7 Bridge road
5   9 Bridge road
1   23 Bridge road
2   14 Kennington street
4   12 Oxford street

- John Cappelletti

---

但是在肯宁顿街之前排序牛津街是错误的，不是吗？ - Twinkles

@Twinkles 谢谢，我漏掉了尾随的%。太棒了，你发现了它。 - John Cappelletti