CODEWITHSUNDEEP
mysqlstreet-address
ufk
ufk

Reputation: 32124

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

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?

Upvotes: 1

Views: 4072

Answers (4)

Andrew T
Andrew T

Reputation: 357

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;

Upvotes: 3

Alasdair
Alasdair

Reputation: 250

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

Upvotes: 0

Emil Vikstr&#246;m
Emil Vikstr&#246;m

Reputation: 91963

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.

Upvotes: 0

Paul Dixon
Paul Dixon

Reputation: 300935

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

Upvotes: 7

Related Questions