Forum Stats

  • 3,728,082 Users
  • 2,245,542 Discussions
  • 7,853,315 Comments

Discussions

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 characters are 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,

Best Answer

Answers

  • Paulzip
    Paulzip Member Posts: 8,243 Blue Diamond

    This looks like a half house JSON document. Is this actual document JSON?

  • Paulzip
    Paulzip Member Posts: 8,243 Blue Diamond

    Here you go, something like this. I'm splitting on the braces, rather than the content.

    with data(clb) as (
     select 
    '{
    
    adam smith
    
    class:abcd
    
    }
    
    {
    
    xxxyyyy
    
    class:abcd
    
    }
    
    {
    
    zzzz
    
    class:abcd
    
    }'
     from dual 
    )
    , sections(num, clb, start_, end_) as (
     select level, clb, instr(clb, '{', 1, level), instr(clb, '}', 1, level)
     from data
     connect by instr(clb, '}', 1, level) > 0
    )
    select num, substr(clb, start_, end_-start_+1) str
    from sections
    


  • aetl
    aetl Member Posts: 170 Blue Ribbon

    Hello Paulzip,


    Your code is working correctly but i couldn't explain my problem.Yes it is an actual json .Here is a sample jscon code. i have a json file like this . When i tried to insert this it raised check constraint error . Becasue there are two "_id" values .İf i insert id by id not in same time it worked .İ want to split braches every "class_..."


    {

    "_id" : "1000099721",

    "id" : [

    {

    "value" : "55555",

    "schemeName" : "MSISDN",

    "schemeAgencyName" : "xx"

    },

    {

    "value" : "12416408",

    "schemeName" : "CustomerId",

    "schemeAgencyName" : "xx"

    },

    {

    "value" : "441630",

    "schemeName" : "OTP",

    "schemeAgencyName" : "xx"

    }

    ],

    "_class" : "model.salesorder.SalesOrderVBO"

    }

    {

    "_id" : "1000099721",

    "id" : [

    {

    "value" : "6666",

    "schemeName" : "MSISDN",

    "schemeAgencyName" : "xx"

    },

    {

    "value" : "12416408",

    "schemeName" : "CustomerId",

    "schemeAgencyName" : "xx"

    },

    {

    "value" : "441630",

    "schemeName" : "OTP",

    "schemeAgencyName" : "xx"

    },

    {

    "value" : "ffa357ee-9759-42ab-8a98-30ea9d410319",

    "schemeName" : "ShoppingCartId",

    "schemeAgencyName" : "xx"

    },

    {

    "value" : "1000099721",

    "schemeName" : "OrderId",

    "schemeAgencyName" : "xx"

    }

    ],

    "_class" : "model.salesorder.SalesOrderVBO"

    }

  • BluShadow
    BluShadow Member, Moderator Posts: 40,927 Red Diamond

    So, as it's actual JSON you're dealing with, have you considered using the Oracle built-in functionality for JSON data?


  • Paulzip
    Paulzip Member Posts: 8,243 Blue Diamond

    Then why didn't you say it was JSON rather than post an example that wasn't? It just wastes people, like myself's time.


    Which Oracle version?

  • aetl
    aetl Member Posts: 170 Blue Ribbon
Sign In or Register to comment.