Postgres: Extract the last substring in a string

Question

I've got a string like

We would like to approve your call, however you have been denied because of reasons

I want to extract the last appearance of either "approve your call" or "have been denied" on the principal that that indicates whether it was approved or declined. I have to use the specific substrings since this is based on a form, not just "approve" and "decline".

My idea was to use a regexp_match to pull out all appearances then grabbing the last row, however there's no way to get the last row from the result of regexp_match.

Nick Barnes · Accepted Answer · 2016-10-12 20:45:00Z

1

On Postgres 9.4+, you can use WITH ORDINALITY to identify the last row in the regexp_matches result:

SELECT m
FROM regexp_matches('abc', '.', 'g') WITH ORDINALITY r(m,n)
ORDER BY n DESC
LIMIT 1

On older versions, you can do this with a window function (though it does rely on the natural ordering of the result set, which is generally best avoided):

SELECT last_value(m) OVER ()
FROM regexp_matches('abc', '.', 'g') r(m)
LIMIT 1

answered Oct 12, 2016 at 20:45

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

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

1 Comment

aa260022 Over a year ago

Ah! that window function will work. Based on testing the natural_ordering of regexp_matches appears to be the lexical ordering, so I think that will be a good fix

Collectives™ on Stack Overflow

Postgres: Extract the last substring in a string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related