Sunday, October 9, 2016

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;

No comments:

Post a Comment