Within the table I've a listing of customer address, within the street area I wish to copy the road number into house_number area.

For Instance, street Title:

  • 8 Blah Road

  • 604 Blah Road

  • 20A Blah Street

  • 10-15 Blah Lane

  • 42/5 Blah Street

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

Can you really that in SQL Query or PHP?

It will likely be something similar to below:

Break the string from first whitespace.

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

Note: Within this situation the home number is definitely first "word" in street value

Possibly you can write some PHP you can update each row with different Regular Expression?

The RegEx would look something similar to:


And I'd expect $1 to become the home number and $2 to become the road title.