Forum Stats

  • 3,722,875 Users
  • 2,244,428 Discussions
  • 7,850,121 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 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,181 Gold Crown

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

  • Paulzip
    Paulzip Member Posts: 8,181 Gold Crown

    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,871 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,181 Gold Crown

    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.