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?

Same question but for javascript: http://stackoverflow.com/questions/610406/javascript-equivalent-to-printf-string-format — tjati, Jun 21 '15 at 12:47

score 25 · Accepted Answer · answered Jun 21 '15 at 14:25

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:

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

String formatting in Oracle (PL/)SQL

1 Answers1