Substr Instr to get first middle and last name

select
substr(:party_name,1,
instr(:party_name,'|',1,1)-1)FNAME,
SUBSTR(:party_name, INSTR(:party_name,'|', 1, 1)+1,
INSTR(:party_name,'|',1,2)-INSTR(:party_name,'|',1,1)-1)MNAME,
substr(:party_name,instr(:party_name,'|',-1,1)+1)LNAME
from dual;

select :party_name,

  TRIM(SUBSTR(:party_name, 1, INSTR(:party_name, ' ', -1,1))) fname,

    SUBSTR(:party_name, INSTR(:party_name, ' ', -1)+1) lname

  from dual;

/* Formatted on 7/10/2023 3:19:47 PM (QP5 v5.294) */

SET SERVEROUTPUT ON

/

DECLARE

   --i number

   l_count_line_id   NUMBER;

   l_party_name      VARCHAR2 (100);

   l_name            VARCHAR2 (100);

BEGIN

   l_count_line_id := 4;

   l_party_name := '012,345,678,910';

   DBMS_OUTPUT.put_line ('l_party_name   :   ' || l_party_name);

   FOR i IN 1 .. l_count_line_id

   LOOP

      l_name :=

         REGEXP_SUBSTR (l_party_name

                      , '[^,]+'

                      , 1

                      , i);

      DBMS_OUTPUT.put_line ('l_name   :   ' || l_name);

   END LOOP;

END;