Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

String formatting in Oracle (PL/)SQL

Tags:

oracle

plsql

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?

like image 952
tjati Avatar asked Jun 21 '15 12:06

tjati


People also ask

What is string formatting in Oracle(PL/SQL)?

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.

What is a format model in Oracle?

A format model does not change the internal representation of the value in the database. When you convert a character string into a date or number, a format model determines how Oracle Database interprets the string.

What is string in PL SQL?

PL/SQL - Strings. The string in PL/SQL is actually a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters or a combination of all. Fixed-length strings − In such strings, programmers specify the length while declaring the string.

How do I format a column in SQL*Plus?

Within the COLUMN command, identify the column you want to format and the model you want to use: If you specify a width shorter than the column heading, SQL*Plus truncates the heading. See the COLUMN command for more details. To set the width of the column LAST_NAME to four characters and rerun the current query, enter


1 Answers

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.
like image 94
Nick Krasnov Avatar answered Oct 21 '22 09:10

Nick Krasnov