Oracle LPAD Function: A Simple Guide to Left-Padding Strings

Have you ever needed to format your query results so they line up perfectly? For example, making all your ID numbers a fixed length (like 001, 023, 100)? The Oracle LPAD function is the perfect tool for this.

LPAD stands for "Left Pad." It makes a string a specific length by adding characters to the left of it. This guide will explain its syntax and show you two practical examples.

What is the LPAD Function in Oracle?

The LPAD function takes a string and "pads" it on the left with a specific character (or set of characters) until it reaches a total target length.

This is extremely useful for:

• Formatting reports so data columns align.
• Creating fixed-length IDs, like 00045 or EMP001.
• Generating simple charts or visual separators in a SQL query.

LPAD Function Syntax

The syntax for the LPAD function is straightforward:

LPAD(expr1, n, [expr2])

Let's break down each part:

• expr1: This is the original string or column you want to pad (e.g., 'Hello' or product_name).
• n: This is the total length you want the final string to be (e.g., 10).
• [expr2] (Optional): This is the string or character you want to use for padding (e.g., '0' or '.').
  • If you don't provide this, Oracle will automatically use a single space ' ' as the default.

One important rule: If your original string (expr1) is already longer than the target length (n), LPAD will shorten the string from the right until it fits. For example, LPAD('Hello World', 5) would just return 'Hello'.

Oracle LPAD Function Examples

Here are two practical examples of how to use LPAD in your Oracle SQL queries.

Example 1: Padding Numbers with Leading Zeros with LPAD

This is the most common use for LPAD. Imagine you have a table of invoices with invoice_id numbers, and you want all of them to be 6 digits long for a report.

We can use DUAL with UNION ALL to simulate this table data.

Query:

-- This query mimics a table with invoice IDs of different lengths
SELECT 
  original_id,
  LPAD(original_id, 6, '0') AS "Formatted ID"
FROM (
  SELECT '45' AS original_id FROM DUAL UNION ALL
  SELECT '1024' AS original_id FROM DUAL UNION ALL
  SELECT '98765' AS original_id FROM DUAL
);

Result:

ORIGINAL_ID  Formatted ID
-----------  ------------
45           000045
1024         001024
98765        098765

As you can see, LPAD added the correct number of leading zeros to each string to make them all 6 characters long.

Example 2: Formatting a List with Dots with LPAD

Another common use is to format text for a simple report, like a table of contents, by padding it with dots or spaces.

Let's say we want to make all our product names appear to be 20 characters long, filling the empty space on the left with dots.

Query:

-- This query mimics a product list
SELECT 
  product,
  LPAD(product, 20, '.') AS "Formatted Product"
FROM (
  SELECT 'Apples' AS product FROM DUAL UNION ALL
  SELECT 'Oranges' AS product FROM DUAL UNION ALL
  SELECT 'Strawberries' AS product FROM DUAL
);

Result:

PRODUCT       Formatted Product
------------  --------------------
Apples        ...............Apples
Oranges       ..............Oranges
Strawberries  ........Strawberries

This creates a clean, right-aligned list that is easy to read.