Expression to extract part of an address [closed]

Question

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.

Answer 1

Maybe some Python list and string functions can help.

temp = text.split(',')

makes an array of everything that was separated by commas.

temp[0]

contains '111 Example St'

temp2 = temp[0].split()

splits by spaces and contains ['111', 'Example','St']

temp2[1:]

contains ['Example','St']

' '.join(temp2[1:])

contains 'Example St'