how to find year_month_date from a string in snowflake - Stack Overflow

admin管理员组

文章数量:1122832

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.

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.

• snowflake-cloud-data-platform
• snowflake-schema

Share Improve this question Follow asked Nov 21, 2024 at 11:48 SaloniSaloni 52722 gold badges1313 silver badges3131 bronze badges 1

• You could also try substr and get the date, select to_date(substr('IN_2001_02_23_guid' ,4,10) , 'YYYY_MM_DD') from dual; – Himanshu Kandpal Commented Nov 21, 2024 at 13:51

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

@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

If your format will always be similar to the example, the easy way is to replace all characters that are not digits with '' using REGEXP_REPLACE.

select regexp_replace(val,'[^0-9]','') as y_m_d from tbl;

Y_M_D
20010223

本文标签： how to find yearmonthdate from a string in snowflakeStack Overflow