mysql: how can i remove the street number from a streetaddress column?

Question

I have a column named streetaddress that contains

<Street Number> <Street Name>

for example:

15 rue Gontier-Patin
4968 Hillcrest Circle

how can i remove the numbers from the beginning of the row purely in sql?

score 7 · Accepted Answer · answered Jan 13 '10 at 12:00

7

How about something like this - trim off everything up to and including the first space in strings which start with a number

UPDATE mytable 
SET addresscol=SUBSTRING(addresscol, LOCATE(' ', addresscol)+1)
WHERE addresscol REGEXP '^[0-9]';

answered Jan 13 '10 at 12:00

Paul Dixon

295,876
54
310
348

Beware of special cases. A street number may contain additions like "15A" or "4968b" or "340-364". – Pekka Jan 13 '10 at 12:01
None of those special cases would foil the approach given :) But yes, any approach like this should be tested on the available data first! – Paul Dixon Jan 13 '10 at 12:09
Excellent use of the REGEXP and substring. I actually used this in my order by clause to properly order the list of address for a customer so that the where essentially order by 'street' that was needed in a drop down. The result came out very well. SELECT propertyId, TRIM(address) FROM Properties order by IF(address REGEXP '^[0-9]', SUBSTRING(address, LOCATE(' ', address)+1), address) – Newbyman Aug 20 '13 at 03:04

score 3 · Answer 2 · answered Oct 16 '12 at 20:06

3

This is based on @Paul Dixon above but is just for returning results without updating the data for things like sorting:

SELECT IF(address REGEXP '^[0-9]', SUBSTRING(address, LOCATE(' ', address)+1), address) AS addrSort FROM table;

answered Oct 16 '12 at 20:06

Andrew T

367
2
14

score 0 · Answer 3 · answered Jan 13 '10 at 12:02

MySQL does not have regexp replace functions so it depends on the values in your column. If the string always begins with a number and then a space, you can do it with the SQl query Paul Dixon posted (doh, he was faster than me :D ).

If you need regular expressions to solve it, you have to do it in application code outside the database.

score 0 · Answer 4 · answered Jan 13 '10 at 12:15

I think this should do the job on the basis that each entry has it's street address seperated from the house number by a space (" ");

UPDATE table
SET streetaddress = MID(streetaddress, LOCATE(' ', streetaddress) + 1);

I've tested this and it works fine.

You can also use the following if you'd like to extract the house number to a new column previous to the update;

UPDATE table
SET housenumber = MID(streetaddress, 1, LOCATE(' ', streetaddress) - 1);

mysql: how can i remove the street number from a streetaddress column?

4 Answers4