Search code examples
regexplsqlplsqldeveloperregexp-replaceregexp-substr

How to select the last 3 words from a string in PL\SQL?

I have a column with data like these:

Professor Dr. Eigen Foster Criminalist

Student Natalie Portman Journalist

Victor Morgan Dentist

Swiss Based Dr. M. Muriel Bayes Jorunalist

What I want to see is:

Eigen Foster Criminalist

Natalie Portman Journalist

Victor Morgan Dentist

Muriel Bayes Jorunalist

I know I should do it somehow with regexp_substr, but I dont know how to use the $ for the starting position. So there could different number of words in a string, but i always need the last 3 words.

Solution

  • Try this regex [^ ]+ [^ ]+ [^ ]+$

    SELECT  regexp_substr('Professor Dr. Eigen Foster Criminalist', '[^ ]+ [^ ]+ [^ ]+$')FROM dual;
    • $ - means end of the string
    • [^ ]+ - means at least one character but no spaces