clob data into multiple columns

Question

First row in table:

1A:CAD22021828,17:1B:RECEIVE GENERAL IND11 BEGUM ST:1C: C/O PNBB MAIN BRANCH 11 BEGUM ST 3-15A2:

Second row in table:

:1A:/002429389016 CITY OF NAGAL AP IN 392H 329 :1E://NAGOLE STREET NAGAL AP :2A:/154510002 OFD DEPOSITORY LTD :

Now i want this data to be loaded in another table as below:

1st row:

1A  CAD22021828,17  
1B  RECEIVE GENERAL IND 11 BEGUM ST /000061071257
1C  C/O PNBB MAIN BRANCH 11 BEGUM ST 3-15A2

2nd row:

1A  /002429389016 CITY OF NAGAL AP IN 392H 329
1E  //NAGOLE STREET NAGAL AO
2A  /154510002 OFD DEPOSITORY LTD

I have tried REGEXP_SUBSTR

SELECT
 REGEXP_SUBSTR(mc_clob,':1A:([[:alnum:]]+\S+)') AS code1A,
 REGEXP_SUBSTR(mc_clob,':1B:([[:alnum:]]+\s+)') AS code1B,
 REGEXP_SUBSTR(mc_clob,':1C:([[:alnum:]]+\s+)') AS code1c
FROM tableA;

1st row:

1A  CAD22021828,17  
1B  RECEIVE GENERAL IND 11 BEGUM ST /000061071257
1C  C/O PNBB MAIN BRANCH 11 BEGUM ST 3-15A2

2nd row:

1A  /002429389016 CITY OF NAGAL AP IN 392H 329
1E  //NAGOLE STREET NAGAL AO
2A  /154510002 OFD DEPOSITORY LTD

Answer 1

This is a special case of the general problem how do i split delimited values

Try this. I'm assuming you have some key in your tableA - here I'm calling it "id". If you don't have a unique key in this table, let me know and I'll rewrite it to use rowid.

-- sample data
with tablea as (select 1 as id, ':1A:CAD22021828,17:1B:RECEIVE GENERAL IND11 BEGUM ST:1C:     C/O PNBB MAIN BRANCH 11 BEGUM ST 3-15A2:' as mc_clob from dual
           union select 2 as id, ':1A:/002429389016 CITY OF NAGAL AP IN 392H 329 :1E://NAGOLE STREET NAGAL AP :2A:/154510002 OFD DEPOSITORY LTD :' from dual)
-- query
SELECT id, 
 regexp_substr(mc_clob, '[^:]+', 1, level*2-1) as code,
 trim(regexp_substr(mc_clob, '[^:]+', 1, level*2)) as val
FROM tableA
connect by regexp_substr(mc_clob, '[^:]+', 1, level*2) is not null
      and PRIOR id =  id 
      and PRIOR SYS_GUID() is not null;

Output:

ID  CODE VALUE
1   1A   CAD22021828,17
1   1B   RECEIVE GENERAL IND11 BEGUM ST
1   1C   C/O PNBB MAIN BRANCH 11 BEGUM ST 3-15A2
2   1A   /002429389016 CITY OF NAGAL AP IN 392H 329
2   1E   //NAGOLE STREET NAGAL AP
2   2A   /154510002 OFD DEPOSITORY LTD

This query won't handle it well if you have blank data in your fields. But perhaps it'll help.