1

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.

uglycat5
  • 101
  • 5
  • 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 Feb 09 '22 at 16:07
  • I think that I'm looking to output the data as a record.. [PL/SQL record](https://www.oracletutorial.com/plsql-tutorial/plsql-record/) – uglycat5 Feb 09 '22 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 Feb 09 '22 at 20:13

1 Answers1

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
Kombajn zbożowy
  • 8,755
  • 3
  • 28
  • 60
  • 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. – uglycat5 Feb 09 '22 at 17:22
  • You can always dynamically build SQL for various inputs. – Kombajn zbożowy Feb 09 '22 at 17:29
  • I realize that I could also use that regex_substr approach and define views based on the stringified data. – uglycat5 Feb 09 '22 at 21:10