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.

Gary_W · Accepted Answer · 2022-08-31 14:21:51Z

0

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+'' $');

answered Aug 31, 2022 at 14:21

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

REGEXP_SUBSTR Redshift - how to find the last substring

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related