Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle String Functions / Oracle LPAD

Oracle LPAD

The Oracle LPAD() function returns a string left-padded with specified characters to a certain length.

Syntax

The following shows the syntax of the Oracle LPAD() function:

LPAD(source_string, target_length [,pad_string]);

Arguments

The Oracle LPAD() function takes three arguments:

1) source_string

is the string that will be padded from the left end.

2) target_length

is the length of the result string after padding.

Note that if the target_length is less than the length of the source_string, then LPAD() function will shorten down the source_string to the target_length without doing any padding.

3) pad_string

is the string to be padded.The pad_string argument is optional. If you don’t specify it explicitly, the LPAD() function will use a single space for padding.

Return value

The LPAD() function returns a string with left-padded characters. The result string is VARCHAR2 or NVARCHAR2, depending on the data type of the source_string.

Examples

The following example pads a string with the character (*) to a length of 5:

SELECT LPAD( 'ABC', 5, '*' ) FROM dual;

The result is:

'**ABC'

In this example, the source string 'ABC' has length 3, therefore, only two more characters need to be padded to make the length of the result string 5.

Consider the following statement.

SELECT LPAD( 'ABCDEF', 5, '*' ) FROM    dual;

In this example, the length of the source string 'ABCDEF' is 6. However, the target length is 5, therefore, the LPAD() function trims 1 character from the source string which results in the following string:

'ABCDE'

We often use the LPAD() function to add leading zeros to format numeric strings. See the following statement:

SELECT LPAD( '123', 8, '0' ) RESULT FROM dual UNION SELECT LPAD( '7553', 8, '0' ) FROM dual UNION SELECT LPAD( '98753', 8, '0' ) FROM dual UNION SELECT LPAD( '754226', 8, '0' ) FROM dual;
Oracle LPAD Function Example

In this example, the lengths of the source strings are different. However, we used the LPAD() function to add the leading zeros to make the lengths of all strings equal to 8.

We also often use the LPAD() function to format the output of a query.

See the following employees table in the sample database:

employees table

The following statement uses the LPAD() function to format the employee names based on his position in the reporting structure:

SELECT employee_id, level, LPAD( ' ',( level - 1 ) * 3 ) || last_name || ', ' || first_name full_name FROM employees START WITH manager_id IS NULL CONNECT BY manager_id = prior employee_id;
Oracle LPAD - Format Query Output Example

In this tutorial, you have learned how to use the Oracle LPAD() function to left-pad a string to a certain length.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle LTRIM
Next Oracle REPLACE

Oracle Functions

  • Aggregate Functions
  • Analytic Functions
  • Comparison Functions
  • Date Functions
  • String Functions

String Functions

  • ASCII
  • CHR
  • CONCAT
  • CONVERT
  • DUMP
  • INSTR
  • INITCAP
  • LENGTH
  • LOWER
  • LPAD
  • LTRIM
  • REPLACE
  • REGEXP_COUNT
  • REGEXP_INSTR
  • REGEXP_LIKE
  • REGEXP_REPLACE
  • REGEXP_SUBSTR
  • RPAD
  • RTRIM
  • SOUNDEX
  • SUBSTR
  • TRANSLATE
  • TRIM
  • UPPER

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.