1

I have a string that contains values such as "String1...String2..." etc. I am trying to use the Oracle regexp_replace built-in to replace these with "StringA...StringB" and so on. I have done the regex pattern to find the strings and can reference the two parts of the pattern separately as demonstrated by the \1-----\2 in the replace string arguement:

with d as (select '<PatFactors><String1>0</String1><String2>0</String2><String3>0</String3><String4>0</String4><String5>0</String5><String6>0</String6><String7>0</String7><String8>0</String8><String9>0</String9><String10>0</String10><String11>0</String11><String12>0</String12><String13>0</String13><String14>0</String14><String15>0</String15><String16>0</String16><String17>0</String17><String18>0</String18><String19>0</String19><String20>0</String20><String21>0</String21><String22>0</String22><String23>0</String23><String24>0</String24><String25>0</String25><String26>0</String26><String27>0</String27><String28>0</String28><String29>0</String29><String30>0</String30></PatFactors>' as o_requestdata
             from dual)   
select regexp_replace(o_requestdata, '([Ss]tring)([0-9]+)', '\1-----\2'/*||chr(ascii('A')-1+to_number('\2'))*/ ) from d;

What I would like to do is keep the back reference \1 and then pass \2 into an expression like the one that is commented out. Unfortunately this gets an invalid number error if I uncomment this section.

Does anyone know if it is possible to perform operations on a back reference like this or can you think of any alternatives other than doing a straight replace 30 times for each value?

Improve this question
1
  • Do you expect <PatFactors><String27>0</String27></PatFactors> to be transformed to <PatFactors><String[>0</String[></PatFactors>? The resulting string would be invalid XML. Commented Nov 20, 2024 at 22:41

2 Answers 2

Reset to default
1

The problem is that to_number() knows nothing about backreferences. You need the backreference from the regular expression function in order to pass to to_number() but \2 has no meaning outside of the regular expression call, so it is the chicken before the egg, so to speak.

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

2 Comments

I figured that this would be the case, was just interested to see if anyone knew of a suitable workaround.
You should be able to workaround it, its just that the solution may be a bit unwieldy. You should be able to write a SQL statement that does full replacement of all patterns matching the regex with replacements. I just doubt it will be a 1-2 liner replacement for what you have.
0

Provided your numbers don't exceed 26 (since Z and [ have the ASCII codes 90 and 91, respectively, and [ is not valid in the name of an XML element), you can use FLWOR expressions in an XML query to modify the tag names:

UPDATE d
SET o_requestdata = XMLSERIALIZE(
              CONTENT XMLQUERY(
                'copy $e := .
                 modify (
                   for $a in $e/*/*[matches(name(), "^(string)([0-9]+)$", "i")]
                     return rename node $a as concat(
                         replace(name($a), "[0-9]+$", ""),
                         codepoints-to-string(
                           64 + xs:integer(replace(name($a), "^string", "", "i"))
                         )
                       )
                 )
                 return $e'
                PASSING XMLTYPE(o_requestdata)
                RETURNING CONTENT
              )
              AS CLOB  
            );

Which, for the sample data:

CREATE TABLE d (o_requestdata) AS
SELECT EMPTY_CLOB() || '<PatFactors><String1>0</String1><String2>0</String2><String3>0</String3><String4>0</String4><String5>0</String5><String6>0</String6><String7>0</String7><String8>0</String8><String9>0</String9><String10>0</String10><String11>0</String11><String12>0</String12><String13>0</String13><String14>0</String14><String15>0</String15><String16>0</String16><String17>0</String17><String18>0</String18><String19>0</String19><String20>0</String20><String21>0</String21><String22>0</String22><String23>0</String23><String24>0</String24><String25>0</String25><String26>0</String26></PatFactors>'
FROM   DUAL;

Updates the table to:

O_REQUESTDATA
<PatFactors><StringA>0</StringA><StringB>0</StringB><StringC>0</StringC><StringD>0</StringD><StringE>0</StringE><StringF>0</StringF><StringG>0</StringG><StringH>0</StringH><StringI>0</StringI><StringJ>0</StringJ><StringK>0</StringK><StringL>0</StringL><StringM>0</StringM><StringN>0</StringN><StringO>0</StringO><StringP>0</StringP><StringQ>0</StringQ><StringR>0</StringR><StringS>0</StringS><StringT>0</StringT><StringU>0</StringU><StringV>0</StringV><StringW>0</StringW><StringX>0</StringX><StringY>0</StringY><StringZ>0</StringZ></PatFactors>

fiddle

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.