Oracle sql split text into rows — oracle-tech

    Forum Stats

  • 3,715,496 Users
  • 2,242,776 Discussions
  • 7,845,366 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Oracle sql split text into rows

aetl
aetl Member Posts: 170 Blue Ribbon
edited November 2020 in SQL & PL/SQL

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
    DStrack Member Posts: 4 Blue Ribbon

    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
    Solomon Yakobson Member Posts: 18,063 Black Diamond

    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.

Sign In or Register to comment.