String formatting in Oracle (PL/)SQL

Question

Modern programming languages allows the developer to create strings with placeholders and replaced the correct values with a function/method usually called format. Sometimes, it looks like this:

"Hi {0}! How are you?".format('John');

Is there any function in Oracle SQL or PL/SQL with the same behavior? Or what's the best practice here?

Answer 1

utl_lms package, and specifically format_message() procedure of that package can be used to format a string.

begin
  dbms_output.put_line(utl_lms.format_message('Hi %s! How are you %s?.'
                                             , 'John'
                                             , 'John'
                                             )
                       );
end;

Result:

Hi John! How are you John?.

It should be noted that:

1. It works only within a PLS/SQL block, not SQL.
2. You should provide substituting value for every substituted special character (%s for string, %d for numbers) even if they are the same.