SQL查询(排序街道地址) [英] SQL Query (sort street address)

问题描述

我有一个要按照以下顺序排序的街道地址列表:

I have a list of street addresses that I want to sort in the following order:

• 街道1
• 街27 A
• 街27楼
• 30 D街
• 31 D街
• 205街
• B街207 B

在一条简单的order by街道上，我得到:

When I do a simple order by street, I get:

Street 1, Street 205, Street 207 B, Street 27 A, Street 27 F, Street 30 D, Street 30 D 

那不是我想要的.

我找不到合适的例子，这是一个此处但这是相反的.

I can't find any good example out there, here is one here but this is the opposite.

推荐答案

考虑到您已将地址表组织为两列road和house_number(后者带有补码)，以下查询(PostgreSQL)可以解决问题(一条独特的道路"The Road"的订单地址):

Consider you have organized your address table in two columns road and house_number (the latter with complements) the following query (PostgreSQL) does the trick (order addresses for a distinct road 'The Road'):

SELECT road, house_number from address_table
WHERE road = 'The Road'
ORDER BY CAST(regexp_replace(house_number, '[[:alpha:]]', '') AS INTEGER),
         regexp_replace(house_number, '[[:digit:]]', '')

似乎也有使用正则表达式替换MySQL的方法:

There seem to be approaches to replace using regular expression with MySQL too: How to do a regular expression replace in MySQL?

这篇关于SQL查询(排序街道地址)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！