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

user_163072 0
Viewed 672

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+).*')
snowflake-cloud-data-platform regex
1 Answers

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:

4000422

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
user_50708790
Related