0

I have a database table which stores web transactions for a fundraising donation page. One of the columns in the table stores a bunch of concatenated data in the format 'data1=value1 data2=value2 data3=value3 moredata4=value3..."

The pairs represent fields/columns from an external database integrated thru a rest service. God knows why it's stores in this way, but I need to parse this column into multiple columns. I cannot figure out how to do this in an efficient and clean way.

Something to note: not all records/rows in the table have a value for every item in the combined column. So you might see 'data1=value1 data2= data3=data3...' also not all 'firlds/columns' in the concatenated pairs start with 'data', some might say 'tranamt=50 coupon=2bb2' for example. Also to note, the 'space' looking character between pairs is actually chr(10) not a space.

I'd really appreciate anyone's help on this. I'm stumped. I've researched this endlessly on stackexchange and Google, and not been able to find anything that helps me with this unique scenario. I've split delimited columns before, but not with a 'field/column' and value pair. I'm also new to oracle. This is in an oracle db.

Thanks!

  • What DB version are you using? Data looks like JSON but isn't. Convert to JSON (or fix source to send JSON) and parse with JSON_TABLE() if available (db version dependent). – Michael Kutz Nov 15 at 11:17
2

dbfiddle

There surely are, a lot of, Oracle experts who can write more optimized code (in Oracle) than this fiddle.... 😉

  • Thank you Luuk - your fiddle has absolutely made my day. I truuuuuly appreciate your taking the time to read my post, and to come up with a thoughtful solution and demonstration to boot. You are a rock-star in my book. And to be honest, I'm not well enough trained or practiced in SQL to grasp much in the way of optimization. I'm self-taught, and until recently had the luxury of a locally hosted server and database that could take whatever non-optimized sql I wrote. My eyes are just opening to all that SQL has to offer beyond basic joins, math, and text maniuplation. Thanks for this – Tyler Hahn Nov 15 at 13:08
  • p.s. I voted your response up, but as i am using a new stack-exchange acct after losing my old acct password my input is not displayed for the public. – Tyler Hahn Nov 15 at 13:09
  • Nice piece of fiddly SQL - +1! – Vérace Nov 15 at 13:36
  • hiya luuk, would you mind helping me understand your regex expression? i.e. '[a-zA-z0-9|:]+=[a-zA-z0-9|:]*' I can't seem to figure it out. I'm not getting exactly the right results, as certain pairs of fields/values aren't getting picked up - namely fields which don't have 'dataNUMBER' as the field name format. I apologize for not referencing that variation eralier. I'll put better sample data here: data9=MONTHLY data8=1 data7=R COUNTRY=US data6=TEST: VI data5=USD data4=10.00 PAY_PROCESS_COMPLETE_DATE=10/15/2019 14:16:48 data1=3505047 ADDRESS_ALTLINE1=136 Radio Hall – Tyler Hahn Nov 19 at 14:06
  • The regular expression [a-zA-Z09] will match any character in the alfabet and numbers. the '+` will match this 1 or more times, the * will match this 0 or more times. In your example data6=TEST: VI will find data6=TEST (the '=' is not in the regular expression), and 'VI' is not recognized because there's no '=' between the part before '=' and after '='. – Luuk Nov 23 at 11:14
1

I'd access this first at the point where those data are created - replace whatever mechanism you use (looks like self-built using carriage-return chr(10) as divider ) - by a proper json serialization - which enables simple json de-serialization

To parse the existing data in said column - you can use SUBSTR https://www.techonthenet.com/oracle/functions/substr.php together with INSTR https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1103.htm#OLADM564 functions of Oracle - but I suggest you do it outside the database in some programming language of your choice

first substr up to the first occurance of chr(10) .. and so forth - problem is to get oracle to accept "data" as column-names . hence the suggestion to do it outside the database in a program / script

  • Thanks eagle275 - i appecaite your rsponse. You are right that the data is coming from a JSON web service. I have no insight into the back-end consumption of the data, or transformation into the database. The database is from a cloud-hosted CRM which allows me SQL access, but from a very limited view. I could do this in R, which i'm sure is not the tool for it - but one i'm relatively adept at, ish, but my goal is to do it in SQL so I can replicate my query or script to end-users who will run it. I can use transact and p/l in the database/crm. I'd apprecaiate any specifics you or others have. – Tyler Hahn Nov 15 at 13:04
  • ignore my request for specifics, Luuk provvided a great fiddle for me to get going. I'd of course graciously welcome anyone else's input or thoughts, for the community's sake as well as mine, but Luuk has answered more than sufficiently for my particular purposes. – Tyler Hahn Nov 15 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.