Oracle 18c

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 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


create table mytable (fullname) as
  select 'SZAFLARSKI,ANDRZEJ S' from dual union all
  select 'SZAFLARSKI rel,ANDRZEJ S' from dual union all
  select 'SZAFLARSKI,ANDRZEJ Santa' from dual union all
  select 'SZAFLARSKI rel,ANDRZEJ' from dual;
4 rows affected

select
  fullname,
  case when fullname like '% _'
    then regexp_substr(fullname, '([^,]+) .$', 1, 1, 'i', 1)
    else regexp_substr(fullname, '[^,]+$')
  end as first_name,
  case when fullname like '% _'
    then regexp_substr(fullname, '.$')
    else ''
  end as middle_name,
	regexp_substr(fullname, '^[^,]+') as last_name
from mytable;


FULLNAME
FIRST_NAME
MIDDLE_NAME
LAST_NAME


SZAFLARSKI,ANDRZEJ S
ANDRZEJ
S
SZAFLARSKI

SZAFLARSKI rel,ANDRZEJ S
ANDRZEJ
S
SZAFLARSKI rel

SZAFLARSKI,ANDRZEJ Santa
ANDRZEJ Santa
null
SZAFLARSKI

SZAFLARSKI rel,ANDRZEJ
ANDRZEJ
null
SZAFLARSKI rel

FULLNAME	FIRST_NAME	MIDDLE_NAME	LAST_NAME
SZAFLARSKI,ANDRZEJ S	ANDRZEJ	S	SZAFLARSKI
SZAFLARSKI rel,ANDRZEJ S	ANDRZEJ	S	SZAFLARSKI rel
SZAFLARSKI,ANDRZEJ Santa	ANDRZEJ Santa	null	SZAFLARSKI
SZAFLARSKI rel,ANDRZEJ	ANDRZEJ	null	SZAFLARSKI rel