I want to split the address into two columns as streetno and streetname, say for ex
select address1 from customer
address for ex-look like
2719 STONE CREEK DR
and store them into street no as 2719
and streetname as STONE CREEK DR
.
using split(address1 ,'[\ ]')[0]
just split the street no only.
it's just select statement we are using to view the data.
Sample data:
address1
100 HORACE GREELEY RD
55 School Street
2893 MASHIE CIR
1200 JEWEL DR
201 W RIVER RD
Expected output
+--------------+---------------------+
| streetnumber | streetname |
+--------------+---------------------+
| 100 | HORACE GREELEY RD |
| 55 | School Street |
| 2893 | MASHIE CIR |
| 1200 | JEWEL DR |
| 201 | W RIVER RD |
+--------------+---------------------+