parse stringified object into row in oracle sql

Question

We have a custom stringified object format that I'd like to explode into an object (or row?) inside of Oracle - with the goal of inserting it into a corresponding table.

The format looks like.. {key=value}:{key1=value1"}:{key2=value2}

e.g. {street=123 Main St}:{city=Somewhere}:{state=MI}:{zip=12345}

The approach I've taken in other languages generally looks like..

function packed2obj(packed_s) {
    packed_s = packed_s.match(/^\{(.+)\}$/);
    var packed_o = new Object();
    var pieces = packed_s[1].split("}:{");

    for (var i = 0; pieces != null && i < pieces.length; i++) {
        match = pieces[i].match(/([^=]*)=(.*)/);
        var key = new String(match[1]);
        var value = new String(match[2]);
        packed_o[key] = value;
    }
    return packed_o;
}

I found this post: pl-sql-split-string-into-an-associative-array, which was somewhat helpful, but it returns each key value pair as a separate row. I was hoping for something that might more easily inserted into a table with (mostly) matching columns (a record maybe?)

Parsing JSON into oracle sql also looks interesting, but the text isn't JSON format. It might be possible to convert it to JSON before it hits the database, but it would be more convenient to the upstream systems if we could stick with the native format.

I'm not sure that I understand exactly what output you are looking for. In your example, would you want a collection of keys and values (which is what your code sample produces? A defined object with street, city, state, and zip attributes? Some sort of dynamically created object whose attributes depend on the keys of the string passed in? Something else? — Justin Cave
– Justin Cave, Commented Feb 9, 2022 at 16:07
I think that I'm looking to output the data as a record.. PL/SQL record — uglycat5
– uglycat5, Commented Feb 9, 2022 at 17:19
@uglycat5- OK. So you're going to define a record type with street, city, state, and zip attributes and then expect those specific keys in the string passed in? Rather than, say, an associative array where the key state has a value 'MI'? — Justin Cave
– Justin Cave, Commented Feb 9, 2022 at 20:13

Kombajn zbożowy · Accepted Answer · 2022-02-09 17:15:03Z

1

Group extraction with regular expressions to the rescue (regexp_substr)!

SELECT regexp_substr(line, '{street=([^}]+)', 1, 1, NULL, 1) as street,
       regexp_substr(line, '{city=([^}]+)', 1, 1, NULL, 1) as city,
       regexp_substr(line, '{state=([^}]+)', 1, 1, NULL, 1) as state,
       regexp_substr(line, '{zip=([^}]+)', 1, 1, NULL, 1) as zip
  FROM (SELECT '{street=123 Main St}:{city=Somewhere}:{state=MI}:{zip=12345}' line FROM dual);


STREET      | CITY      | STATE | ZIP
------------+-----------+-------+------
123 Main St | Somewhere | MI    | 12345

answered Feb 9, 2022 at 17:15

Kombajn zbożowy

10.8k5 gold badges33 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

uglycat5 Over a year ago

We've got several different types of data stored in this format, so I was hoping for something a bit more generic, but I hadn't considered regex here. If I have to fall back to defining something for each possible data type, this is certainly an option.

Kombajn zbożowy Over a year ago

You can always dynamically build SQL for various inputs.

uglycat5 Over a year ago

I realize that I could also use that regex_substr approach and define views based on the stringified data.

Collectives™ on Stack Overflow

parse stringified object into row in oracle sql

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related