CODEWITHSUNDEEP
sqlregexoracle-database
devoured elysium
devoured elysium

Reputation: 105217

Replace part of a string of a text field with Regex pattern in Oracle SQL

I have a (long) JSON string on an Oracle DB column that I need to update. I'm lucky that the update will only happen on a relatively restricted number of rows (~50) so performance is not paramount. Yet, as always, we'll not want it to be particularly inefficient also.

The goal of the update query is to find and modify a field in the JSON:

... "myField":{"$bigdec":"1.23"} ...

I'll need to take out 1.23, cast it as a (decimal) number, apply a formula to it and put the result back in.

Is there any built-in Oracle function that would allow me to find an expression and modify it? The only way I can think off the top of my head is to find the indices i and j, of the beginning and ending of 1.23 and then to 

SUBSTR(json, 1, i-2) || newNumber || SUBSTR(json, j+1)

Ideally the last think I'd like to do is have to fiddle with the i and j indices as I just did, and to have to run several SUBSTR calls, etc.

Is there something better than this? Thanks

Upvotes: 1

Views: 2265

Answers (2)

user5683823
user5683823

Reputation:

Here's something that may work:

with
     inputs ( seq, str ) as ( 
       select 1, 'alpha:"123.k"..."myField":{"$bigdec":"1.23"} and others' 
       from   dual
     ),
     nbr    ( seq, n ) as (
       select seq, 
              regexp_substr(str, '"myField":{"\$bigdec":"(\d+\.?\d*)"}', 1, 1, null, 1) 
       from   inputs
     ),
     repl   ( seq, r_str ) as (
       select seq, to_char( round(power(to_number(n), 3), 2), 'fm99999999.99') 
       from   nbr
     )
select i.seq, i.str,
       regexp_replace(i.str, '"myField":{"\$bigdec":"\d+\.\d*"}', 
                               '"myField":{"\$bigdec":"' || r.r_str || '"}') as repl_str
from   inputs i inner join repl r on i.seq = r.seq
;

 SEQ STR                                                      
---- ---------------------------------------------------------
   1 alpha:"123.k"..."myField":{"$bigdec":"1.23"} and others

                                  REPL_STR
                                  --------------------------------------------------------
                                  alpha:"123.k"..."myField":{"\$bigdec":"1.86"} and others

Here I took 1.23, converted it to a number, raised it to the third power, rounded the result to two decimal places, converted to string (pay attention to the fm in the format model, without it you will get an extra space - which is there as placeholder for sign, + or -) and replaced in the input string.

The sequence number, and the inner join on it at the end, is needed to keep each input string separate; if you don't have a unique column in your input table already, which you could use for this purpose, you will need to create one first. Also, pay close attention to the $, which I needed to escape with \ (you'll see it easily if you know what to look for, but you could have missed it if I didn't alert you to it).

Good luck!

Upvotes: 1

Alim Özdemir
Alim Özdemir

Reputation: 2634

You can use the REGEXP_REPLACE function for that which (as the name implies) allows regular expression substitutions (example takes out the first 3 chars and has them in group1 if you need it in the new number.

update TABLENAME
set json = REGEXP_REPLACE(json, '^(...)', 'before\1afteroldnumber)

or just

update TABLENAME
set json = REGEXP_REPLACE(json, '1.23', 'newNumber')

there are also modifiers for 'global' and such, as the example above will only swap the first occurance. Best test first with:

select json, REGEXP_REPLACE...

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm

Upvotes: 1

Related Questions