@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