How to perform string interpolation in Oracle PLSQL and SQL. Given a text template with named placeholders, substitute those with variables

Question

Question: How to perform string interpolation is Oracle using PLSQL? Given a template with named parameters enclosed within curly braces then substitute named parameters for variables. Functionality similar to languages like C# string interpolation, Java. The code should be optimised for performance, though mostly will be used infrequently.

The curly braces are mandatory as the templates are business user defined and held in tables. This is a standard convention used in the company. The target is to "mail merge" business documents and snippets in Oracle itself.

Implementation: Preferably package functions that operate both in PLSQL and SQL. For SQL a csv list of name value pairs will be passed in example 'name, jane, age, 26'. name, value, name, value etc...

Example use case:

Note the use of double curly braces as an escape for the literal '{'. The template will be stored in a database table, the substitution variables will be derived at run-time based on complex rules. The example below is just simple:

template = 'Hi my name is {name}, I am aged {age}, yes {age}! Replaced placeholders {{name}}, {{age}}'

variables = name: jane, age: 26

output = Hi my name is Jane, I am aged 26, yes 26! Replaced placeholders {name} and {age}

Example PLSQL with call to imaginary package:

DECLARE v_template NVARCHAR2(500);
        v_name NVARCHAR2(50) := 'Jane';
        v_age NVARCHAR2(50) := '26';
        v_output NVARCHAR2(1000); TYPE ty_dictionary IS TABLE OF NVARCHAR2(1000) INDEX BY VARCHAR2(200);
        v_vars ty_dictionary;
BEGIN

   -- note, escaped double curly braces {{ will output {,
   v_template := 'Hi my name is {name}, I am aged {age}, yes {age}! Replaced placeholders {{name}}, {{age}}';
   v_vars('name') := v_name;
   v_vars('age') := v_age;

   v_output:= pkg_interpolation.fn_format(v_template, v_vars);
   -- should output:
   -- Hi my name is Jane, I am aged 26, yes 26! Replaced placeholders {name} and {age}
   dbms_output.put_line('output: ' || v_output);
END;

Erythritol

Answer 1

Try something like this (db<>fiddle):

declare 
    type vararg is table of varchar2 (96) index by varchar2 (32);
    
    function format (template varchar2, args vararg) return varchar2 is
        key varchar2 (32);
        ret varchar2  (32767) := template;
        pattern varchar2 (32) := '(^|[^{]){(\w+)}([^}]|$)';
    begin
        <<substitute>> loop
            key := regexp_substr  (ret, pattern, 1, 1, null, 2);
            exit substitute when key is null;
            ret := regexp_replace (ret, pattern, 
                '\1'||case when args.exists (key) then args (key) else '?'||key||'?' end||
                '\3', 1, 1);
        end loop;
        return replace (replace (ret, '{{','{'), '}}', '}');
    end;
begin
    dbms_output.put_line ('output: '||format (q'[
{name} said: Hi my name is {name}, I am aged {age}, yes {age}! 
Missing key {somekey}; Replaced placeholders {{name}}, {{age}}. Again I am {age}]',
        vararg ('name' => 'Jane', 
                'age'  => '26')));
end;
/

output:
Jane said: Hi my name is Jane, I am aged 26, yes 26!
Missing key ?somekey?; Replaced placeholders {name}, {age}. Again I am 26

Rainarainah

Thanks!! If the template repeats placeholders, then the placeholders AFTER an escaped placeholder is not substituted. For example 'Hi my name is {name}, I am aged {age}, yes {age}! Replaced placeholders {{name}}, {{age}}. Again I am {age}' output is [Hi my name is Jane, I am aged 26, yes 26! Replaced placeholders {name}, {age}. Again I am {age}] The last {age} is not replaced. Any ideas why? – Erythritol

@Erythritol ... Again I am {age} - there is no match for the last character. Please see on db<>fiddle. – Rainarainah

brilliant that is now working. Marked your answer as complete. Thanks again! – Erythritol

Answer 2

utl_lms.format_message() seems to do most of what you want:

declare
    v_template nvarchar2(500)  := 'Hi my name is %s, I am aged %d.';
    v_name     nvarchar2(50)   := 'Jane';
    v_age      nvarchar2(50)   := '26';
    v_output   nvarchar2(1000) := utl_lms.format_message(v_template, v_name, v_age);
begin
    dbms_output.put_line('output: ' || v_output);
end;

output: Hi my name is Jane, I am aged 26.

You could probably write a wrapper to supply the rest of the functionality you want such as the ability to pass in a collection of values.

Theiss

Thanks William, I would require named placeholders as these are stored in the DB by users. The placeholders in UTL_LMS.FORMAT_MESSAGE are limited to special names %s, %d. Plus do not provide for curly braces – Erythritol

Answer 3

If you have APEX installed, you could use the wwv_flow_utilities.fast_replace_manyf function.

It's also available as a procedure: wwv_flow_utilities.fast_replace_many.

SELECT wwv_flow_utilities.fast_replace_manyf(
          p_srcstr => 'Hi my name is {name}, I am aged {age}, yes {age}! Replaced placeholders {{name}}, {{age}}',
          oldsub   => wwv_flow_t_varchar2('{{', '}}', '{name}', '{age}', CHR(0), CHR(1)),
          newsub   => wwv_flow_t_varchar2(CHR(0), CHR(1), 'jane', '26', '{', '}')) newtext
  FROM DUAL;

--Output: Hi my name is jane, I am aged 26, yes 26! Replaced placeholders {name}, {age}

I've added four extra values to the substitution strings to handle the curly braces as this isn't built-in, but this will fail if your source text includes CHR(0) or CHR(1), and can be easily avoided by choosing longer substitutions.

Gloriane