Oracle LPAD

LPAD is a function used in Oracle that returns a left padded string of ‘n’ characters, padded with a text value.

The syntax of Oracle LPAD function is as follows:

LPAD( Expression, n, ‘pad_value’)

Here,

Expression/Column Name – Expression could be a column name or string. Basically, the string that will be padded.

n (Length)– n is the number of characters we want as an outcome. If we want the string to be 10 characters long, then we give n =10

Note: If the value of n is less than the no. of characters already present in the string, then LPAD will trim the Expression to ‘n’ characters. The final string will always contain ‘n’ characters.

pad_value – This could be a special character, text, or even an empty string that will be used as padding. If we don’t enter any pad_value then Oracle will use single space as padding.

The data type of outcome is usually VARCHAR2 or NVARCHAR2 depending on the data type of source.

Oracle LPAD Examples

Suppose, we want to want to use left padding to create a ’10’ character string by padding letters ‘xyz’ with ‘#’, then we use this query:

SELECT
LPAD( 'xyz', 10, '#' ) LPAD
FROM
dual;

Result:

The query returns a string which has 10 characters in total and we used left padding for letter ‘xyz’ while the remaining characters were ‘#’.

Let’s see one example, where we use LPAD to trim some characters as the total length of the string is less than the total number of characters in the word.

SELECT
LPAD( '123456', 4, '#' ) LPAD
FROM
dual;

Result:

The query returns first 4 characters as the total length of string is 4.

We will be using Employee table from HR schema for this example,

Let’s use a query to pad the salary of each employee in Employee table with 0. Here, we give the total length of string as 10 and the character used for left-padding is 0.

SELECT first_name, LPAD(salary,10, 0) salary
FROM employees;

Result:

The query returns each employee’s salary with left padding as 0.

Let’s use LPAD to try a slightly more complex query where we arrange Employee’s based on their hierarchy in the organization.

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;

Result: