Oracle sql split text into rows

aetl

Hello Everyone ,

I have a clob column which has value like this :

{

adam smith

class:abcd

}

{

xxxyyyy

class:abcd

}

{

zzzz

class:abcd

}

class:abcd text is common all between {}

İ want to split into rows after each class:abcd} :

row 1:{

adam smith

class:abcd

}

row2:{

adam smith

class:abcd

}

row3:{

adam smith

class:abcd

}

I tried to use connect by but i didnt do it exactly.

How can i split this text into rows ?

Regards,

DStrack

use the function apex_string.split to split clobs. (available since APEX 19.2)

select s.column_value from clob_tab, table(apex_string.split(clob_column,'class:abcd'||chr(10)||'}')) s;

Solomon Yakobson

Assuming you are on 12C or higher:

with sample as (
                select '{
adam smith
class:abcd
}
{
xxxyyyy
class:abcd
}
{
zzzz
class:abcd
}' data from dual
)
select  rownum,
        r
  from  sample,
        lateral(
                select  regexp_substr(data,'{[^}]*}',1,level) r
                  from  dual
                  connect by level <= regexp_count(data,'{')
               )
/

    ROWNUM R
---------- --------------------
         1 {
           adam smith
           class:abcd
           }

         2 {
           xxxyyyy
           class:abcd
           }

         3 {
           zzzz
           class:abcd
           }

SQL>

And if performance is a factor you can change REGEXP_SUBSTR with SUBSTR + INSTR and REGEXP_COUNT with LENGTH + REPLACE.

SY.

Paulzip

Duplicate of this