So we have a table called TEMPLATES that stores html email templates as plain text (don't ask, I wasn't around when that decision was made). There are a number of columns in this table but the one we care is the TEMPLATE field which is where the text is stored.

Now we recently updated the libraries used by the server to parse these templates before they get sent to the user. These libraries use a new syntax for variables


My problem is a lot of the old email templates use the old syntax


These variable names contain only alpha-numeric characters. I'm trying to use REGEXP_REPLACE to replace all instances of @whateverVariable@ with ${whateverVariable} but I'm having difficulty preserving the variable name when I do the replacement. Is this possible? Is there a better way to do this?

Edit: I should probably mention the field is a CLOB if that makes a difference.

  • 1
    CLOB or VARCHAR2 makes no difference (here) (ORACLE DOCS), use a simple UPDATE like UPDATE TEMPLATES set template = REPLACE(template,'@whateverVariable@','${newVariable}'); , REGEXP_REPLACE is a little bit too heavy for this job and you know your variableNames. – Patrick7 Sep 21 '16 at 17:20
  • @Patrick7 Yeah, but I'm trying to run one one script that will fix all variables regardless of the actual variable name. I don't know all the variable names, nor do I want to run the script each time for every variable in every template. – Lee Abraham Sep 21 '16 at 17:39
  • Do you think that showing your regular expression, along with the current and expected results, might help? – mustaccio Sep 21 '16 at 18:27

Your variables have a fixed format and 3 parts:

  • the opening @
  • variable name
  • the closing @

With the below regexp, you can keep the 2nd part (\2) and add the extra characters around it you need (${ }):

with data as
    to_clob('@ @@ ABC @variable1@ DEF @something2@ GHI @variable3@ @@@')
      as template 
  from dual
  regexp_replace(template, '(@)([[:alnum:]]+)(@)', '${\2}') as new_template
from data;

@ @@ ABC @variable1@ DEF @something2@ GHI @variable3@ @@@
@ @@ ABC ${variable1} DEF ${something2} GHI ${variable3} @@@

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.