Split varchar2 variable based on vertical line delimiter

Question

I have following plsql code

DECLARE
   v_personal_info VARCHAR2(100);
   v_name VARCHAR2(100);
   v_signature VARCHAR2(100);
   CURSOR c_personal_info
   IS
     select personal_info from users where name is not null;
BEGIN
  FOR lr_row IN c_personal_info
  LOOP
    v_personal_info := lr_row.personal_info;
    -- Need to split v_personal_info based on |
  END LOOP;
END;
/

Typical values of v_personal_info will look like 'Aniket Thakur | athakur' and I need to split this to different variables like

v_name := 'Aniket Thakur'
v_signature := 'athakur'

How should I do that. Is there any in built function that does a split based on predefined delimiter?

score 1 · Accepted Answer · edited May 23 '17 at 12:30

If the values of v_personal_info are uniform and always of the structure string_1+|+string_2, then you can easily use REGEXP_SUBSTR.

In that case, your procedure can be written as follows:

DECLARE
   v_personal_info VARCHAR2(100);
   v_name VARCHAR2(100);
   v_signature VARCHAR2(100);
   CURSOR c_personal_info
   IS
     select personal_info from users where name is not null;
BEGIN
  FOR lr_row IN c_personal_info
  LOOP
    v_personal_info := lr_row.personal_info;
    v_name := REGEXP_SUBSTR ( v_personal_info, '^[^|]*');
    v_signature := REGEXP_SUBSTR (v_personal_info,'[^|]*$')
  END LOOP;
END;
/

Of course practically the procedure is not useful unless you write these values somewhere.

However, if you want a generic way of splitting strings using different delimiters, then you'll have to create your own function. There are multiple examples here to do that. See this & this

There is very nice article written by one of the SO users Split single comma delimited string into rows in Oracle

score 0 · Answer 2 · answered Dec 05 '15 at 11:26

Hawks answer works fine but I prefer not to use regex so I use simple SUBSTR and INSTR methods.

DECLARE
   v_personal_info VARCHAR2(100);
   v_name VARCHAR2(100);
   v_signature VARCHAR2(100);
   delimitIndex number;
   CURSOR c_personal_info
   IS
     select personal_info from users where name is not null;
BEGIN
  FOR lr_row IN c_personal_info
  LOOP
    v_personal_info := lr_row.personal_info;
    delimitIndex := INSTR(v_personal_info, '|');
    if delimitIndex != 0 then
        v_name := SUBSTR(v_personal_info, 1, delimitIndex - 1);
        v_signature := SUBSTR(v_personal_info, delimitIndex+1, LENGTH(v_personal_info)-delimitIndex);
    END if;
  END LOOP;
END;
/

Split varchar2 variable based on vertical line delimiter

2 Answers2