oracle - Using SUBSTR and INSTR to get the suffix of a Name -

i need suffix out of last name. example, may last names suffix this: smith jr or smith jr. or smith lcsw, or smith j.a.c.g., or this: abardo torres jr or abbas feinberg lcsw, etc.

i know have use substr , instr, i'm stuck. can following , can't make work. can me?

subtr(last_name,instr(last_name,' ',1) + 1      ,(instr(last_name,' ', instr(last_name,' ',1)+1 - (instr(last_name,' ',1)+1) 

even if can make works may works last name consist of 1 word + suffix such smith jr not work last name consist of 2 words such abardo torres jr

select    last_name,   regexp_replace(last_name, '\s*((esq|kbe|msc|bsc|jr|lcsw|phd|j\.a\.c\.g)\.?\s*)*$')      name_without_suffix,   regexp_substr(last_name, '((esq|kbe|msc|bsc|jr|lcsw|phd|j\.a\.c\.g)\.?\s*)*$')      suffix your_table 

---

to last word in string:

regexp_substr(string, '(\s+)\s*$', 1, 1, 'i', 1) 

fiddle