Separate street name from house number?

Question

In the table I have a list of customer address, in the street field I want to copy the street number into house_number field.

For Example, street Name:

• 8 Blah Road

• 604 Blah Road

• 20A Blah Street

• 10-15 Blah Lane

• 42/5 Blah Street

So the house number would be: 8, 604, 20A, 10-15, 42/5

Is it possible to that in SQL Query or PHP?

Answer 1

Perhaps you could write some PHP you could update each row based on a Regular Expression?

The RegEx would look something like:

(\d+\w*)\s(.*)

And I'd expect $1 to be the house number and $2 to be the street name.

Answer 2

It will be something like below:

Break the string from first whitespace.

 update table set house_number=SUBSTRING_INDEX(street," ");

Note: In this case the house number is always first "word" in street value