Regular expression to remove suffix from last name

Question

I need to create a regular expression to remove suffixes from the last name. suppose the last name is like the below tables. I need this regex for pl/sql(oracle). Thank you in advance.

Input string   -> expected output

    SMITH      -> SMITH
    SMITH JR.  -> SMITH
    SMITH ,JR  -> SMITH
    SMITH JR I -> SMITH
    SMITH /SR  -> SMITH
    SMITH S    -> SMITH S ( S is not present in the predefined suffix list)

Predefined list includes JR, SR, I II, III ,IV, V

So, there's a list of predefined suffixes? Does it mean that you have to remove everything that's in that list, and leave everything else? If so, I guess you should share that list with us. — Littlefoot, Sep 30 '22 at 10:29
Too bad, other members voted to close the question so I can't post query which (kind of) returns result you wanted. I'm not sure whether it'll be reopened or not, but - if you posted a new question, I'll post that query so that you could see whether it helps or not. — Littlefoot, Sep 30 '22 at 11:30

Abra · Answer 1 · 2022-10-01T11:21:06.333

I created a separate database table, name SUFFIXES, to hold the list of suffixes.

create table SUFFIXES (SUFFIX varchar2(5))

I created another database table to hold the surnames.

create table SURNAMES (SURNAME varchar2(20))

Use LISTAGG to get a regular expression containing all the suffixes with alternation.

select listagg(SUFFIX, '|') from SUFFIXES

Use REGEXP_REPLACE to first replace unwanted punctuation.

select regexp_replace(SURNAME, ',|/') from SURNAMES

Note that in the sample data in your question there is only comma (',') and forward slash ('/').

Use another REGEXP_REPLACE on the result of the above to then remove the suffixes.

select regexp_replace((regexp_replace(SURNAME, ',|/')), (select listagg(SUFFIX,'|') from SUFFIXES)) as RESULT
  from SURNAMES
 where SURNAME like ('SMITH%')

Refer to this db<>fiddle.

By the way – not that it affects the question or answer – according to Guide to Writing Men's Names with Suffixes, if grandfather, father and son all have the same name, then the grandfather's suffix is Sr, the father's suffix is Jr and the son's suffix is III. In other words there is no I suffix nor is there a II suffix.

Regular expression to remove suffix from last name

1 Answers1