JK87 JK87 - 3 years ago 92
MySQL Question

Split address column in streetname and number using SELECT statement

I have a mysql table with an address column.
Now I need to SELECT the streetname and number separately.

Address

Wallstreet 20
New Yorkavenue 30
New London Street 40


Needs to be:

Street: Number:

Wallstreet 20
New Yorkavenue 30
New London Street 40


Any ideas?
Thanks in advance!

Answer Source

If you assume that the number is the final "word" and separated by a space:

select replace(address, substring_index(address, ' ', -1), '') as street,
       substring_index(address, ' ', -1) as number

I happen to think that those two assumptions are very big assumptions, meaning that this might not work on all your rows.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download