I have an input table in snowflake with column contains data pattern as follows
city, state/LOCATION/designation
city state/LOCATION/designation
city, state/LOCATION
Want to extract only location and store in another column, can you help me doing this?
You could use SPLIT_PART, as mentioned in a previous answer, but if you wanted to use regular expressions I would use REGEXP_SUBSTR, like this:
REGEXP_SUBSTR(YOUR_FIELD_HERE,'/([^/]+)',1,1,'e')
To break it down, briefly, it's looking for a slash and then takes all the non-slash characters that follow it, meaning it ends just before the next slash, or at the end of the string.
The 1,1,'e' correspond to: starting at the first character of the string, returning the 1st match, and extracting the substring (everything in the parentheses).
Snowflake documentation is here.