Extract particular text from String in Snowflake

Question

I m new to snowflake.

Input String : ["http://info.wealthenhancement.com/ppc-rt-retirement-planning"]

Output String : info.wealthenhancement.com/ppc-rt-retirement-planning

Please help to get output string.

Thanks

Answer 1

Use the substr function to only take characters from the 8th character to the end:

select 
    'http://info.wealthenhancement.com/ppc-rt-retirement-planning' as orig_value,
    substr(orig_value, 8) as new_value

The output is:

+-------------------------------------------------------------+-------------------------------------------------------+
|ORIG_VALUE                                                   | NEW_VALUE                                             |
+-------------------------------------------------------------+-------------------------------------------------------+
|http://info.wealthenhancement.com/ppc-rt-retirement-planning | info.wealthenhancement.com/ppc-rt-retirement-planning |
+-------------------------------------------------------------+-------------------------------------------------------+

Answer 2

This will work for http and https URLs by splitting using // as a delimiter. Only the last statement is required. The other two show how it's done built into steps:

-- Set a session variable to the string
set INPUT_STRING = '["http://info.wealthenhancement.com/ppc-rt-retirement-planning"]';

-- Trim leading and trailing square brackets and double quotes
select (trim($INPUT_STRING, '"[]'));

-- Split using // as a delimiter and keep only the right part and cast as string
select split((trim($INPUT_STRING, '"[]')), '//')[1]::string as URL