Sample Header Ad - 728x90

Snowflake - extracting country/ state names - RegEx or Array?

0 votes
1 answer
304 views
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.
Asked by user45867 (1739 rep)
Jun 5, 2023, 09:32 PM
Last activity: May 14, 2025, 08:00 AM