I have a column of addresses and want to separate into several parts.
Ex. house number, street name, city, zip. For example lets say I have a cell with this string: '111 Example St, City, State 89786 zipcode, country'
. This is how I'd like to separate it:
house_numb | street_name | city | state | zip | country |
'111' | 'Example St' | 'City' | 'State' | '89786' | 'US'
I was able to use a left regex expression to create the house number. Since the city, zip, state, and country are the same I was able to populate those columns as well. But I cannot seem to get the street name in the string. What expression can be used to extract the street name?
I tried regexp_substr( "Text", ';(\\d*)\}' )
and replaced the ';'
with ' '
and '}'
with ','
and did not get the street name between the first space and the first comma.