A Guide to Oracle LPAD and RPAD Functions: From Basics to Applications

The LPAD and RPAD functions are extremely useful tools in Oracle Database for formatting strings to a specific length. This article introduces their basic usage to advanced examples in a structure that is easy for beginners to understand. It also provides practical examples and best practices using the SCOTT schema.

This article uses examples based on the SCOTT sample schema.

You can import and use the SCOTT sample schema by executing the following:

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger

1. What are LPAD and RPAD?
2. Basic Syntax and How They Work
1. LPAD Syntax
2. RPAD Syntax
3. Practical Examples Using the SCOTT Schema
4. Advanced Usage
5. Points to Note and Best Practices
6. Exercises
1. Problem 1: Left-aligning Product Code
2. Problem 2: Applying a Custom Format
7. Summary

1. What are LPAD and RPAD?

LPAD and RPAD are Oracle SQL functions for padding strings to a specified length.

LPAD: Pads the left side with a specified character.
RPAD: Pads the right side with a specified character.

They are used in various situations, such as data formatting, report creation, and improving the readability of logs.

2. Basic Syntax and How They Work

LPAD Syntax

LPAD(string, length, padding_char)

RPAD Syntax

RPAD(string, length, padding_char)

Argument	Description
`string`	The target string.
`length`	The final length of the string.
`padding_char`	The character used to fill the space. If omitted, a space is used.

3. Practical Examples Using the SCOTT Schema

3.1 LPAD Example: Zero-padding Employee Numbers

The following SQL formats the EMPNO column’s value into a 5-digit string.

SELECT EMPNO, LPAD(EMPNO, 5, '0') AS PADDED_EMPNO
FROM EMP;

Result

SQL> SELECT EMPNO, LPAD(EMPNO, 5, '0') AS PADDED_EMPNO
  2  FROM EMP;

     EMPNO PADDED_EMPNO
---------- --------------------
      7369 07369
      7499 07499
      7521 07521
      7566 07566
      7654 07654
      7698 07698
      7782 07782
      7788 07788
      7839 07839
      7844 07844
      7876 07876
      7900 07900
      7902 07902
      7934 07934

14 rows selected.

3.2 RPAD Example: Formatting Job Titles

Formats the JOB column’s value into a 10-character string, filling the extra space with *.

SELECT JOB, RPAD(JOB, 10, '*') AS PADDED_JOB
FROM EMP;

Result

SQL> SELECT JOB, RPAD(JOB, 10, '*') AS PADDED_JOB
  2  FROM EMP;

JOB       PADDED_JOB
--------- ----------------------------------------
CLERK     CLERK*****
SALESMAN  SALESMAN**
SALESMAN  SALESMAN**
MANAGER   MANAGER***
SALESMAN  SALESMAN**
MANAGER   MANAGER***
MANAGER   MANAGER***
ANALYST   ANALYST***
PRESIDENT PRESIDENT*
SALESMAN  SALESMAN**
CLERK     CLERK*****
CLERK     CLERK*****
ANALYST   ANALYST***
CLERK     CLERK*****

14 rows selected.

3.3 Visual Data Formatting

Concatenates the employee name (ENAME), right-aligned to a 10-character width, and the job title (JOB), left-aligned to a 10-character width.

SELECT 
    LPAD(ENAME, 10, ' ') || RPAD(JOB, 10, '.') AS FORMATTED_RESULT
FROM EMP;

Result

SQL> SELECT
  2      LPAD(ENAME, 10, ' ') || RPAD(JOB, 10, '.') AS FORMATTED_RESULT
  3  FROM EMP;

FORMATTED_RESULT
-------------------------------------------------------
     SMITHCLERK.....
     ALLENSALESMAN..
      WARDSALESMAN..
     JONESMANAGER...
    MARTINSALESMAN..
     BLAKEMANAGER...
     CLARKMANAGER...
     SCOTTANALYST...
      KINGPRESIDENT.
    TURNERSALESMAN..
     ADAMSCLERK.....
     JAMESCLERK.....
      FORDANALYST...
    MILLERCLERK.....

14 rows selected.

4. Advanced Usage

4.1 Zero-padding Numeric Data

Zero-pad a product code to 8 digits.

SELECT LPAD(PRODUCT_ID, 8, '0') AS PADDED_ID
FROM PRODUCTS;

4.2 Padding with Multiple Characters

LPAD and RPAD can use repeating patterns of multiple characters.

SELECT RPAD('TEST', 10, 'AB') AS PADDED_STRING
FROM DUAL;

Result:

SQL> SELECT RPAD('TEST', 10, 'AB') AS PADDED_STRING
  2  FROM DUAL;

PADDED_STR
----------
TESTABABAB

4.3 Adjusting Column Width

Create easy-to-read reports, such as for invoices.

SELECT 
    LPAD(ENAME, 15, ' ') AS NAME_COLUMN,
    RPAD(JOB, 15, ' ') AS JOB_COLUMN
FROM EMP;

Result

SQL> SELECT
  2      LPAD(ENAME, 15, ' ') AS NAME_COLUMN,
  3      RPAD(JOB, 15, ' ') AS JOB_COLUMN
  4  FROM EMP;

NAME_COLUMN          JOB_COLUMN
-------------------- --------------------
          SMITH      CLERK
          ALLEN      SALESMAN
           WARD      SALESMAN
          JONES      MANAGER
         MARTIN      SALESMAN
          BLAKE      MANAGER
          CLARK      MANAGER
          SCOTT      ANALYST
           KING      PRESIDENT
         TURNER      SALESMAN
          ADAMS      CLERK
          JAMES      CLERK
           FORD      ANALYST
         MILLER      CLERK

14 rows selected.

5. Points to Note and Best Practices

Handling NULL: If the target string is NULL, the result will also be NULL.
Length Constraint: Strings longer than the specified length will be truncated.
Impact on Performance: Padding operations are relatively lightweight, but if applying them to large amounts of data, confirm index validity.

6. Exercises

Problem 1: Left-aligning Product Code

Execute the following code and check the result where the product code is left-aligned.

SELECT RPAD('1234', 10, '#') AS PADDED_CODE FROM DUAL;

Problem 2: Applying a Custom Format

Let’s consider the result of right-aligning employee names with asterisks and left-aligning job titles with hyphens.

SELECT 
    LPAD(ENAME, 8, '*') || RPAD(JOB, 8, '-') AS FORMATTED_ROW
FROM EMP;

7. Summary

LPAD and RPAD are extremely powerful tools for string formatting. They truly shine when adjusting data output formats or creating reports. Please use this article as a reference to master their practical applications!

Next Steps:

Utilize them in combination with other string functions (SUBSTR, INSTR, LENGTH, etc.).
Try using padding for formatted output in an actual report.

[reference]
Oracle Database SQL Language Reference, 19c