snowflake-cloud-data-platformsnowflake-schema

how to find year_month_date from a string in snowflake

I am using snowflake to get some data. I have one requirement that i want to extract year_month_day from a string.

a string would be 'IN_2001_02_23_guid' and I want to extract 20010223 from this string.

Could you please help me to find this?

Thank you.

Solution

  • @Dave's answer is probably the best for your use case if the pattern is well defined.Otherwise REGEXP_SUBSTR can be used for cases when it is needed to extract a substring that matches a regular expression pattern.

    Please note : This function doesn't modify the string; it simply returns the part of the string that matches the pattern.

    Solution using REGEXP_SUBSTR :

    SELECT REGEXP_SUBSTR('IN_2001_02_23_guid', '\\d+', 1, 1) || 
REGEXP_SUBSTR('IN_2001_02_23_guid', '\\d+', 1, 2) || 
REGEXP_SUBSTR('IN_2001_02_23_guid', '\\d+', 1, 3) AS extracted_date;

    Explanation :

    \\d+ represents digits 
REGEXP_SUBSTR('IN_2001_02_23_guid', '\\d+', 1, 1)  -- 1,1 means start from position 1 and pick up the first match i.e 2001
REGEXP_SUBSTR('IN_2001_02_23_guid', '\\d+', 1, 2)  -- 1,2 means start from position 1 and pick up the second match i.e 02
REGEXP_SUBSTR('IN_2001_02_23_guid', '\\d+', 1, 3)  -- 1,3 means start from position 1 and pick up the third match i.e 23