Skip to Main Content
Forums

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!

Oracle sql split text into rows

aetlNov 25 2020 — edited Nov 27 2020

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,

Comments

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.

1 - 3

Post Details

Added on Nov 25 2020
3 comments
176 views