(PL\SQL) How to parse a full name into first, middle and last names?

1st Method

CLEAR;
with t as (
select 'fabc iabc oabcd' fio from dual union all
select ' fabc        iabc oabcd' fio from dual union all
select 'fabc iabc     oabcd  ' fio from dual union ALL
select '   Baimendinov Salimjan     Danauly   ' fio
       from dual union ALL
select ' fabc     iabc     oabcd   ' fio from dual union all
select 'fabc iabc    ' fio from dual union ALL
select '  fabc   ' fio from dual union ALL
SELECT 'fabc  iabc  ' fio from dual
)
SELECT substr(fio, 1, instr(fio, ' ')) AS LastName,
       substr(fio, instr(fio, ' ') + 1,
       instr(substr(fio, instr(fio, ' ') + 1), ' '))
       FirstName,
       substr(substr(fio, instr(fio, ' ') + 1),
       instr(substr(fio, instr(fio, ' ') + 1), ' ') + 1,
       instr(substr(substr(fio, instr(fio, ' ') + 1),
       instr(substr(fio, instr(fio, ' ') + 1),
       ' ') + 1), ' ')) MiddleName
  FROM
  (
  SELECT
    TRIM(REPLACE(
         REPLACE(REPLACE(fio, ' ', '@!'), '!@'),
         '@!', ' '))
    || ' ' fio FROM t) t;

2nd Method

CLEAR;
with t as (
select 'fabc iabc oabcd' fio from dual union all
select ' fabc        iabc oabcd' fio from dual union all
select 'fabc iabc     oabcd  ' fio from dual union ALL
select '       fabc iabc     oabcd   ' fio from dual union ALL
select 'fabc     iabc     oabcd   ' fio from dual union ALL
select '   Baimendinov Salimjan     Danauly   ' fio
       from dual union ALL
select 'fabc iabc ' fio from dual union ALL
SELECT 'fabc  iabc' fio from dual)
SELECT
trim(substr(trim(fio),1,instr(trim(fio),' ',1,1)))
      AS LastName,
CASE WHEN nvl(length(trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))),1,instr
         (TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1)))),0) = 0
    THEN trim(substr(trim(fio),instr(trim(fio),' ',-1,1),
         length(trim(fio))))
    ELSE trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))),1,
         instr(TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1))) END AS FirstName,
CASE WHEN nvl(length(trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))), 1,
         instr(TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1)))),0) = 0
    THEN 'null'
     ELSE trim(substr(trim(fio),instr(trim(fio),' ',-1,1),
          length(trim(fio)))) END AS MiddleName FROM t;

ResultSet

LASTNAME	FIRSTNAME	MIDDLENAM
——–	———	———
fabc	iabc	oabcd
fabc	iabc	oabcd
fabc	iabc	oabcd
fabc	iabc	oabcd
fabc	iabc	oabcd
Baimendinov	Salimjan	Danauly
fabc	iabc	null
fabc	iabc	null