CODEWITHSUNDEEP
oracle-databaseplsql
tjati
tjati

Reputation: 6079

String formatting in Oracle (PL/)SQL

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?

Upvotes: 12

Views: 13498

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

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.

Upvotes: 27

Related Questions