REGEXP_SUBSTR Redshift - how to find the last substring

Question

I have a question regarding REGEXP_SUBSTR in redshift. I have a string, and I want to always return the last substring 'STATUS CHANGE FROM ''something'' TO ''another thing''. There might be multiple substring in the string. I only need to return the last one.

select REGEXP_substr(' sfdfgdfg STATUS CHANGE FROM ''REGISTERED'' TO ''DISABLED'' ZD# 17564 REASON: CUSTO STATUS CHANGE FROM ''DISABLED'' TO ''HOLD'' ', 'STATUS CHANGE FROM ''\w+'' TO ''\w+'' ');

The current code return nothing. Can someone help me take a look?

Thank you very much.

Answer 1

If I understand you, I believe all you need is to anchor your pattern to the end of the line. This is based on your one line of test data. Note the dollar sign.

select REGEXP_substr(' sfdfgdfg STATUS CHANGE FROM ''REGISTERED'' TO ''DISABLED'' ZD# 17564 REASON: CUSTO STATUS CHANGE FROM ''DISABLED'' TO ''HOLD'' ', 'STATUS CHANGE FROM ''\w+'' TO ''\w+'' $');