SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Parse ";" delimited string into array or rows

PeterValencicJun 23 2016 — edited Jun 23 2016

Hi,

I need help with parsing this kind of rows into array or into rows (pipelined)..

here are two examples of datas:

ASDIRECT;I0050499;6361229001;460000000690;1680,8;28.04.2016;2214;DC;ECC;12;1;360056****1005;007783;

ASDIRECT;;;460000000736.280416114515;1225,12;28.04.2016;1147;VC;PBZ;00;1;457418******3009;030084;

What I need is to get empty or null value for ;;

thank you

DECLARE
  I INTEGER;
  TYPE T_ARRAY_OF_VARCHAR IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
  MY_ARRAY T_ARRAY_OF_VARCHAR;
  MY_STRING VARCHAR2(2000) := 'ASDIRECT;;;460000000736.280416114515;1225,12;28.04.2016;1147;VC;PBZ;00;1;457418******3009;030084;';
BEGIN

  FOR CURRENT_ROW IN (
    with test as   
      (select MY_STRING from dual)
      select regexp_substr(MY_STRING, '[^;]+', 1, rownum) SPLIT
      from test
      connect by level <= length (regexp_replace(MY_STRING, '[^;]+'))  +1)
  LOOP
    DBMS_OUTPUT.PUT_LINE(CURRENT_ROW.SPLIT);
    MY_ARRAY(MY_ARRAY.COUNT) := CURRENT_ROW.SPLIT;
  END LOOP;
END;

output is:

ASDIRECT

460000000736.280416114515

1225,12

28.04.2016

1147

PBZ

457418******3009

030084

what I need:

ASDIRECT

null or ''

460000000736.280416114515

1225,12

28.04.2016

1147

PBZ

457418******3009

030084

This post has been answered by Paulzip on Jun 23 2016

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 21 2016

Added on Jun 23 2016

3 comments

9,493 views