I have a bunch of sloppy Geo text in a field.
However the country/ state names or codes are relatively clean.
It'll say Poland 23 or Illinois Remote or Frogballs, Germany or TX, AL, AK.
I have a finite list of country names/ codes ... and US 50 state names, codes.
I'm trying to figure out the best way to convert the "trash STATENAME trash" into a clean state name or country name.
I'm thinking either go the array route
STRTOK_TO_ARRAY(location_field) - which will convert the string to 'word items' in an array. But I'm not sure the best function to extract a matching 'item' within an array.
Array_contains() merely is true/ false. Not "Poland".
Maybe regex is better for this purpose? Something like
regexp_like(location_field,country_list|country_list,'i'). Only issue here is that -- only want to match countries/ states that are a "word" (preceding or trailing space) -- not "AL" for Alabama when it's part of portugAL for instance.