Extract string after first '/' using snowflake query

Question

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?

Answer 1

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.

Answer 2

There are several ways to do this:

A) using the SPLIT_PART function:

SELECT SPLIT_PART('city, state/LOCATION/designation', '/', 2);

Reference: SPLIT_PART

B) using the SPLIT_TO_TABLE tabular function:

 SELECT t.VALUE
   FROM TABLE(SPLIT_TO_TABLE('city, state/LOCATION/designation', '/')) AS t
  WHERE t.INDEX = 2;

Reference: SPLIT_TO_TABLE

C) using REGEXP expressions:

SELECT REGEXP_REPLACE('city, state/LOCATION/designation', '(.*)/(.*)/(.*)', '\\2');

but this one doesn't work if you don't have a third term ('designation'), you need to combine with two calls and check by number of backslashes.

SELECT IFF(REGEXP_COUNT('city, state/LOCATION', '/') = 1,
           REGEXP_REPLACE('city, state/LOCATION','(.*)/(.*)','\\2'), 
           REGEXP_REPLACE('city, state/LOCATION','(.*)/(.*)/(.*)','\\2'));

Reference: REGEXP_REPLACE