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