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

Question

hi.

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?

Answer 1

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]';

Answer 2

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.

Answer 3

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);