Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
Oracle Database 21c Express Edition Release 21.0.0.0.0 - Production
0


create table SUFFIXES (SUFFIX varchar2(5))
insert into SUFFIXES values ('JR')
1 rows affected

insert into SUFFIXES values ('SR')
1 rows affected

insert into SUFFIXES values ('I')
1 rows affected

insert into SUFFIXES values ('II')
1 rows affected

insert into SUFFIXES values ('III')
1 rows affected

insert into SUFFIXES values ('IV')
1 rows affected

insert into SUFFIXES values ('V')
1 rows affected

create table SURNAMES (SURNAME varchar2(20))
insert into SURNAMES values ('SMITH')
1 rows affected

insert into SURNAMES values ('SMITH JR.)
ORA-01756: quoted string not properly terminated

insert into SURNAMES values ('SMITH ,JR')
1 rows affected

insert into SURNAMES values ('SMITH JR I')
1 rows affected

insert into SURNAMES values ('SMITH /SR')
1 rows affected

insert into SURNAMES values ('SMITH S')
1 rows affected

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


RESULT


SMTH

SMTH

SMTH

SMTH

SMTH S