RPAD

The RPAD function returns Expression1, right-padded to length n characters with Expression2, replicated as many times as necessary. This function is useful for formatting the output of a query.

SQL syntax

RPAD (Expression1, n [,Expression2])

Parameters

RPAD has the parameters:

Parameter	Description
Expression1	CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB operand or column to be right-padded. If Expression1 is longer than n, then RPAD returns the portion of Expression1 that fits in n.
n	Length of characters returned by RPAD function. Must be a NUMBER integer or a value that can be implicitly converted to a NUMBER integer.
Expression2	CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB operand or column to be right-padded to Expression1. If you do not specify Expression2, the default is a single blank.

Description

• If Expression1 is of type CHAR or VARCHAR2, the data type returned is VARCHAR2. If Expression1 is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2. If Expression1 is a LOB, the data type returned is the same as the LOB data type provided.

• The returned data type length is equal to n if n is a constant. Otherwise, the maximum result length of 8300 is returned.

• You can specify TT_CHAR, TT_VARCHAR, TT_NCHAR, and TT_NVARCHAR for Expression1 and Expression2. If Expression1 is of type TT_CHAR or TT_VARCHAR, the data type returned is TT_VARCHAR. If Expression1 is of type TT_NCHAR or TT_NVARCHAR, the data type returned is TT_NVARCHAR.

• For CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB data types:

  • If either Expression1 or Expression2 is NULL, the result is NULL. If n is less than or equal to 0, the result is NULL.

• For TT_CHAR, TT_VARCHAR, TT_NCHAR and TT_NVARCHAR types:

  • If either Expression1 or Expression2 is not NULL and if n is less than or equal to 0, the result is the empty string.

Examples

Concatenate first_name and last_name from the employees table. Call the RPAD function to return first_name right-padded to length 12 with spaces and call RPAD a second time to return last_name right-padded to length 12 with spaces. Select first five rows.

Command> SELECT FIRST 5 CONCAT (RPAD (first_name,12),
           RPAD (last_name,12)) FROM employees
              ORDER BY first_name, last_name;
< Adam        Fripp        >
< Alana       Walsh        >
< Alberto     Errazuriz    >
< Alexander   Hunold       >
< Alexander   Khoo         >
5 rows found.

Call the RPAD function to return last_name right-padded to length 20 characters with the dot ('.') character. Use the employees table and select first five rows.

Command> SELECT FIRST 5 RPAD (last_name,20,'.') FROM employees
           ORDER BY last_name;
< Abel................ >
< Ande................ >
< Atkinson............ >
< Austin.............. >
< Baer................ >
5 rows found.