Oracle RPAD Function: A Simple Guide

The RPAD function in Oracle is the mirror image of LPAD. It "Right Pads" a string, adding characters to the end of it until it reaches a specified total length.

This function is most commonly used for formatting reports, ensuring that text in a column lines up neatly.

What is the RPAD Function in Oracle?

RPAD (Right-Pad) takes a string and makes it longer by adding a specific character (or set of characters) to its right side.

This is useful for:

• Creating simple bar charts or visual displays in SQL.
• Formatting data in fixed-width reports.
• Ensuring all values in a column have the same display length.

RPAD Function Syntax

The syntax for RPAD is simple and mirrors LPAD:

RPAD(expr1, n, [expr2])

Let's break that down:

• expr1: The original string or column you want to pad (e.g., 'First Name').
• n: The total length you want the final string to be. This is a number.
  • If expr1 is already longer than n, RPAD will cut it short (truncate it) to fit n.
• [expr2] (Optional): The character or string you want to pad with.
  • If you omit this, RPAD will default to using a single space.

Oracle RPAD Function Examples

Here are two practical examples of how to use RPAD.

Example 1: Padding with a Specific Character using RPAD

Let's say you want to display a list of categories and pad them all with dots . so they line up in a 20-character-wide column.

Query:

SELECT 
  RPAD('Category 1', 20, '.') AS "Padded Category"
FROM DUAL
UNION ALL
SELECT 
  RPAD('Long Category 2', 20, '.') AS "Padded Category"
FROM DUAL
UNION ALL
SELECT 
  RPAD('Cat 3', 20, '.') AS "Padded Category"
FROM DUAL;

Result:

Padded Category
--------------------
Category 1..........
Long Category 2.....
Cat 3...............

Example 2: Using the Default Space Padding with RPAD

Imagine you are creating a report and you want the last_name column to be exactly 25 characters wide, filling any extra space with blanks. This ensures the next column (like job_id) always starts at the same position.

Query:

-- Using RPAD with the default space padding
SELECT 
  RPAD(last_name, 25) || job_id AS "Formatted Employee"
FROM employees
WHERE ROWNUM <= 3;

Result: (The | is just to show where the 25-character boundary is)

Formatted Employee
--------------------------------|
King                     AD_PRES
Kochhar                  AD_VP
De Haan                  AD_VP

As you can see, job_id starts at the same position for every row, making the report much cleaner.