regex - How can I match the last two words in a sentence in PostgreSQL?

Question

Have been trying for a while, to match the last word of a sentence:

select regexp_matches('My name is Harry Potter', '[^ ]+$');

returned {Potter}

to try to match the last two words:

select regexp_matches('My name is Harry Potter', '[^ ]s+[^ ]+$');

failed.

select regexp_matches('My name is Harry Potter', '(.*?)s+(.*?)$');

Did not word as intended either.

Any insights?

Answer 1

Instead of using REGEXP_MATCHES which returns an array of matches, you may be better off using SUBSTRING which will give you the match as TEXT directly.

Using the correct pattern, as @Abelisto shared, you can do this:

SELECT SUBSTRING('My name is Harry Potter' FROM 'w+W+w+$')

This returns Harry Potter as opposed to {"Harry Potter"}

Per @Hambone's comment, if either of the words at the end contain punctuation, like an apostrophe, you would want to consider using the following pattern:

SELECT SUBSTRING('My name is Danny O''neal' FROM 'S+s+S+$')

The above would correctly return Danny O'neal as opposed to just O'neal