Split Multiline CLOB Column - Oracle PL/SQL

Question

I have a table which includes a CLOB field with values consisting of lines of comma-separated values. In the output I want a row for each these lines which start with a particular value. I also want to extract some of the comma-separated values performantly.

Input table (3 rows):

id         my_clob
001        500,aaa,bbb
           500,ccc,ddd
           480,1,2,bad
           500,eee,fff
002        777,0,0,bad
003        500,yyy,zzz

Target output (4 rows):

id         my_clob          line_num    line           second_val
001        500,aaa,bbb      1           500,aaa,bbb    aaa
           500,ccc,ddd
           480,1,2,bad
           500,eee,fff
001        500,aaa,bbb      2           500,ccc,ddd    ccc
           500,ccc,ddd
           480,1,2,bad
           500,eee,fff
001        500,aaa,bbb      3           500,eee,fff    eee
           500,ccc,ddd
           480,1,2,bad
           500,eee,fff
003        500,yyy,zzz      1           500,yyy,zzz    yyy

This is very similar to this question, but in that case, there was a non-hidden character to split on. A related question removes newline characters. I'd like to split on whatever character(s) are separating these lines. I've tried variants of chr(10) || chr(13) and [:space:]+ without success

My attempt:

SELECT 
   id
   ,my_clob
   ,level as line_num
   ,regexp_substr(my_clob,'^500,\S+', 1, level, 'm') as line 
   ,regexp_substr(
        regexp_substr(
            my_clob,'^500,\S+', 1, level, 'm'
        )
        ,'[^,]+', 1, 2
   ) as second_val
FROM tbl
CONNECT BY level <= regexp_count(my_clob, '^500,\S+', 1, 'm')
  and prior id = id
  and prior sys_guid() is not null

The result is generally only derived from the first line in my_clob, depending on how I adjust the match pattern.

I specify this 'm' match_parameter, according to the Oracle Docs:

'm' treats the source string as multiple lines. Oracle interprets the caret (^) and dollar sign ($) as the start and end, respectively, of any line anywhere in the source string

Anything wrong with these regexp_*(my_clob, '^500,\S+', 1, 'm')? Or better yet, is there a more performant way without regex?

Popeye · Accepted Answer · 2020-07-08T09:44:01.950

You can use the REGEXP as follows:

SQL> -- sample data
SQL> with your_data(id,myclob) as
  2  (select 1, '500,aaa,bbb
  3             500,ccc,ddd
  4             480,1,2,bad
  5             500,eee,fff' from dual)
  6  -- Your query starts from here
  7  select id, myclob, line_num, lines as line,
  8  regexp_substr(lines,'[^,]+',1,2) as second_val
  9  from
 10    (select id, myclob, column_value as line_num,
 11  trim(regexp_substr(d.myclob,'.+',1,column_value,'m')) as lines
 12     from your_data d
 13     cross join table(cast(multiset(select level from dual
 14                                 connect by  level <= regexp_count(d.myclob,'$',1,'m'))
 15     as sys.OdciNumberList)) levels)
 16  where regexp_like(lines,'^[500]');

 ID MYCLOB                    LINE_NUM LINE            SECOND_VAL
--- ------------------------- -------- --------------- ----------
  1 500,aaa,bbb                      1 500,aaa,bbb     aaa
    500,ccc,ddd
    480,1,2,bad
    500,eee,fff

  1 500,aaa,bbb                      2 500,ccc,ddd     ccc
    500,ccc,ddd
    480,1,2,bad
    500,eee,fff

  1 500,aaa,bbb                      4 500,eee,fff     eee
    500,ccc,ddd
    480,1,2,bad
    500,eee,fff


SQL>

Thanks! This is surprisingly fast too when accessing a single table partition in my case. Using the `column_value` pseudo-column from `table` seems to work much better than using the `level` variable. — Wassadamo, Jul 08 '20 at 20:12

Split Multiline CLOB Column - Oracle PL/SQL

1 Answers1

Linked