Snowflake REGEX to get last 7-digit number in a string-CodePudding

I'm having trouble figuring out how to write a regex in Snowflake that will return the last 7-digit number from a string. Non-working example:

SELECT REGEXP_SUBSTR('4000400 - Widget Revenue:4000401 - Special Widgets:4000422 - Tiny Special Widgets', '\D*\d \D (\d ).*')

CodePudding user response：

Using '(\\d{7})(\\D*)$' and accessing first subgroup:

SELECT REGEXP_SUBSTR('4000400 - Widget Revenue:4000401 - Special Widgets:4000422 - Tiny Special Widgets'
     ,'(\\d{7})(\\D*)$',1,1,'e',1) AS result

Output:

EDIT:

Handling numbers that are shorter than 7 digits after last occurence of 7 digits number:

SELECT REGEXP_SUBSTR('6000700 - Benefit Expense:6000820 - Benefits - 401k Match'
                     ,'(\\d{7})(\\D \\d{0,6}\\D*)*$',1,1,'e',1);
-- 6000820


SELECT REGEXP_SUBSTR('4000400 - Widget Revenue:4000401 - Special Widgets:4000422 - Tiny Special Widgets'
                    ,'(\\d{7})(\\D \\d{0,6}\\D*)*$',1,1,'e',1);  
-- 4000422