SQL Query (sort street address)

Question

I have a list of street 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 order by street, I get:

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

and that is not what I want.

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

You'll need to part out the number value cast it to a numeric value and then sort by the text street name then the number. — xQbert, Aug 11 '14 at 14:06
It's almost impossible to get something coherent, as you may perfectly have a number in the `name` of the street. The best way to go would be to separate 1. Street name 2. HouseNumber 3. HouseNumberComplement — Raphaël Althaus, Aug 11 '14 at 14:06
@RaphaëlAlthaus: can you provide a example how the query would look like — Gilbert92, Aug 11 '14 at 14:12
Well, if you separate your data into 3 fields (StreetName varchar, HouseNumber int, HouseNumberComplement varchar), you could simply do `order by StreetName, HouseNumber, HouseNumberComplement` — Raphaël Althaus, Aug 11 '14 at 14:15

Jochen Schwarze · Answer 1 · 2018-03-05T16:07:02.390

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:]]', '')

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

score 1 · Answer 2 · answered Aug 11 '14 at 14:05

1

ORDER_BY your_Order * 1 ASC

will convert it to a number since it seems to be a text value.

Check the table definition and change it. You can change the data type to int like this

ALTER TABLE your_Table MODIFY COLUMN registration_no int;

answered Aug 11 '14 at 14:05

Fappie.

482
6
18

score 1 · Answer 3 · answered Aug 11 '14 at 15:50

1

If the columns all start with a single word (such as 'Street') followed by a space and a number, then you can do:

order by substring_index(streetaddress, ' ', 2) + 0

This will convert the second "word" in the address to a number which is used for sorting.

If your real data is different from the sample data, I would suggest that you ask another question with appropriate examples of what your data really looks like.

answered Aug 11 '14 at 15:50

Gordon Linoff

1,242,037
58
646
786

Thanks for the answer, but this not work for me, i already got a solution. Going to separate the street-address in 3 values – Gilbert92 Aug 11 '14 at 17:42

score -1 · Answer 4 · answered Apr 27 '16 at 10:57

Try this

With addresses as

(
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

SQL Query (sort street address)

4 Answers4

Linked