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]);
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

The result is:

'**ABC'
Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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'Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

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:

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;
Code language: SQL (Structured Query Language) (sql)

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