Search code examples
snowflake-cloud-data-platformregexp-substrsql-query-store

Extract string after first '/' using snowflake query

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?

Solution

  • 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.