SQL Query (sort street address)
I have a list of addresses that I want to sort in the following order:
- Street 1
- Street 27 A
- Street 27 F
- Street 30 D
- Street 31 D
- Street 205
- Street 207 B
When I do a simple street order by
, I get:
Street 1, Street 205, Street 207 B, Street 27 A, Street 27 F, Street 30 D, Street 30 D
and that's not what I want.
I can't find a good example here here , but it's the other way around.
source to share
If all columns start with one word (for example 'Street'
) followed by a space and a number, you can do:
order by substring_index(streetaddress, ' ', 2) + 0
This converts the second "word" in the address into a number that is used for sorting.
If your actual data differs from the example data, I would suggest that you ask another question with relevant examples of how your data really looks.
source to share
Note that you have ordered your address table in two columns road
and house_number
(the last one is padded), the following query (PostgreSQL) does the trick (addresses for a particular road "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:]]', '')
There seem to be approaches to replacing regex with MySQL: How to replace regex in MySQL?
source to share
try it
With addresses like
(
SELECT
Address1,
Postcode,
CASE WHEN ISNUMERIC(SUBSTRING(LTRIM(Address1), 1, 1)) = 1 THEN 'yes' ELSE 'no' END AS StartsWithNumber,
CASE WHEN ISNUMERIC(SUBSTRING(LTRIM(Address1), 1, 1)) = 1 THEN SUBSTRING(Address1, 1, CHARINDEX(' ', Address1)) END AS housenumber
FROM youttable )
Select Address1 from addresses
order by StartsWithNumber asc, housenumber, Address1
source to share