Regexp substr with start position at the end of the string PL/SQL

Question

I have strings like these:

WORK 123 John Smith

10.01.D 5132 3330 Selena Amirez

300 TK30 000 Edvard Ramirez

I want to write out just the names in the end. I had a code like this:

regexp_substr(string, '([0-9])( +)(.*)', 1, 1, 'i', 3))

But it only works for the first line, which has only one number in it. My idea is to start the writing from the end, because names are always in the end. So I want to write out the names, and the starting position would be always the last digit of the last number.

So what I want to see is:

John Smith

Selena Amirez

Edvard Ramirez

Thank you in advance.

Answer 1

This could be a way:

select regexp_substr(str, '([0-9]+ )([^0-9]*$)', 1, 1, 'i', 2)
from (
    select 'WORK 123 John Smith' str from dual union
    select '10.01.D 5132 3330 Selena Amirez' from dual union
    select '300 TK30 000 Edvard Ramirez' from dual
)

which gives:

Selena Amirez                                    
Edvard Ramirez                                   
John Smith 

This gets the non-numeric rigt part of the string ($ is the end of the string) which follows a numeric part and a space.