Spliting name with delimiter

Question

Hi can you help me to split the name into firstname, middle name, lastname using a pl/sql procedure.

This my procedure:

create or replace procedure proc_name(name in varchar2, a out varchar2, b out varchar2,c out varchar2) is
begin
    if regexp_count(name, '/') = 2 then
      a := regexp_substr(name, '[^/]+', 1, 1);
      b := regexp_substr(name, '[^/]+', 1, 2);
      c := regexp_substr(name, '[^/]+', 1, 3);
    end if;
end proc_name;

Here is what it should do. Input: sharmi/devi/elango

The output is:

 firstname:sharmi
 middle name: devi
 lastname : elango

I got output for above test cases.

Suppose my input is: /devi/

Then my output should be:

 firstname: null
 middle name: devi
 lastname: null

How could I make it work?

Answer 1

One of the problems with the regexp_substr() calls in your procedure is: sometimes, they return incorrect values - which will be assigned to the 3 variables (a,b,c). Suppose that there always are 3 "tokens" (first/middle/last name), delimited by '/'. Each token can either be a "name" or null. Let's generate some test data, and apply the regexes that you have used in your procedure (Oracle 12c):

create or replace view test_data
as
select
  rownum as id
, dt.combinations
, regexp_substr( dt.combinations, '[^/]+', 1, 1) var_a
, regexp_substr( dt.combinations, '[^/]+', 1, 2) var_b
, regexp_substr( dt.combinations, '[^/]+', 1, 3) var_c
from
(
  select
    decode ( T1.token, null, '/', T1.token || '/' )  ||
    decode ( T2.token, null, '/', T2.token || '/' )  ||
    decode ( T3.token, null, '' , T3.token )  as combinations
  from
    ( select null as token from dual union select 'sharmi' from dual ) T1
  , ( select null as token from dual union select 'devi' from dual )   T2
  , ( select null as token from dual union select 'elango' from dual ) T3
) dt ;

Selecting from the view gives us:

select * from test_data;

ID  COMBINATIONS        VAR_A   VAR_B   VAR_C
1   sharmi/devi/elango  sharmi  devi    elango
2   sharmi/devi/        sharmi  devi
3   sharmi//elango      sharmi  elango        -- incorrect (should be: var_a and var_b)
4   sharmi//            sharmi
5   /devi/elango        devi    elango        -- incorrect (should be: var_b and var_c)
6   /devi/              devi                  -- incorrect (should be: var_b)
7   //elango            elango                -- incorrect (should be: var_c)
8   //

Instead of using a procedure, you could use a function (original code: see https://community.oracle.com/thread/925201?start=0&tstart=0 , accessed on 2018-01-06) that returns a table of varchar2, like so:

create or replace type split_tbl as table of varchar2(32767);

create or replace function split3
(
    p_list varchar2,
    p_del varchar2 := '/'
) return split_tbl pipelined
is
    l_idx    pls_integer;
    l_list    varchar2(32767) := p_list;
    l_value    varchar2(32767);
begin
    loop
        l_idx := instr(l_list,p_del);
        if l_idx > 0 then
            pipe row( substr( l_list,1,l_idx-1 ) );
            l_list := substr(l_list,l_idx+length(p_del));
        else
            pipe row(l_list);
            exit;
        end if;
    end loop;
    return;
end split3;
/

For testing this function, you could use something like (explanation below):

declare
  combination_ varchar2(128) := '' ;
  result_ split_tbl ;
  type labels is varray(3) of varchar2(16);
  n_label labels := labels(' first name: ',' middle name: ',' last name: ');
begin
  for i in 1 .. 8
  loop
    select
      ' [test case ' || to_char(id) || ']  string -> ' || combinations
    into combination_ from test_data where id = i;

    select * bulk collect into result_
    from table ( split3( ( select combinations from test_data where id = i ) ) ) ;

    dbms_output.put_line( combination_ ) ;

    for token in 1 .. 3
    loop
      dbms_output.put( n_label(token) ) ;
      if result_(token) is null then  dbms_output.put_line( 'null' ) ;
      else dbms_output.put_line( result_(token) ) ;
      end if;
    end loop;

    dbms_output.put_line( '-------------------' ) ;
  end loop;
end;
/

The anonymous block uses an array of varchar2 values containing labels. It picks up each of our test cases (from the test_data VIEW), calls the "split" function, and labels (and outputs) its return values.

-- output (anonymous block):
[test case 1]  string -> sharmi/devi/elango
 first name: sharmi
 middle name: devi
 last name: elango
-------------------
 [test case 2]  string -> sharmi/devi/
 first name: sharmi
 middle name: devi
 last name: null
-------------------
 [test case 3]  string -> sharmi//elango
 first name: sharmi
 middle name: null
 last name: elango
-------------------
 [test case 4]  string -> sharmi//
 first name: sharmi
 middle name: null
 last name: null
-------------------
 [test case 5]  string -> /devi/elango
 first name: null
 middle name: devi
 last name: elango
-------------------
 [test case 6]  string -> /devi/
 first name: null
 middle name: devi
 last name: null
-------------------
 [test case 7]  string -> //elango
 first name: null
 middle name: null
 last name: elango
-------------------
 [test case 8]  string -> //
 first name: null
 middle name: null
 last name: null
-------------------

One of the advantages of the "split" function is: it can also handle more than 3 tokens. Eg

SQL> select * from table(split3(
  2  'João/Alexandre/Duarte/Ferreira/Fernandes'
  3  ))
  4  ;
COLUMN_VALUE  
João          
Alexandre     
Duarte        
Ferreira      
Fernandes