Oracle sql split text into rows — oracle-tech

    Forum Stats

  • 3,708,983 Users
  • 2,241,169 Discussions
  • 7,840,727 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

aetlaetl 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

  • DStrackDStrack 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 YakobsonSolomon Yakobson Posts: 17,946 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.