sql - 通过自定义比较器在 Housenumber 列上排序 Postgresql 查询结果

Question

我正在使用 postgresql 作为数据库。使用我的查询从具有建筑物信息的表中选择 varchar 类型的列 housenumber(和一些其他列)。所以我希望结果以其他方式排序，而不是字符串比较。

例如，如果我有以下结果:

"1"
"1 block2"
"1 b30"
"1 b3"
"1 b3 s4"
"10"
"2"

我希望此结果按以下逻辑排序:

1. 1) 获取源字符串"1 b3 s4"
2. 2) 将其拆分为 ["1", "b3", "s4"]
3. 3) 尝试将所有子字符串解析为整数，忽略字母，这不是 [1 , 3, 4]
中的数字

4. 4) 为 future 的排序计算更大的数作为

   1 * 1000000 + 3 * 1000 + 4 = 1003004.
   

这可能吗？我如何实现此方法并将其用于对查询结果进行排序？

这是我的 sql 查询(缩写):

SELECT housenumber, name
FROM osm_buildings
where
    housenumber <> ''      
order by housenumber
limit 100

Answer 1

我不确定您为什么要转换为一些大整数进行排序。您可以执行以下操作:

• 删除所有不是数字或空格的字符。
• 转换为数组，在一个或多个空格处拆分。
• 将数组转换为整数数组。

然后你可以对此进行排序:

order by regexp_split_to_array(regexp_replace(v.addr, '[^0-9 ]', '', 'g'), ' +')::int[]

如果你想持久化它，你可以将其作为一个值存储在表中。

Here是一个数据库<> fiddle 。