Oracle/PLSQL LPAD Function

Oracle LPAD Function is used to pad a string with a set of characters from the left side. SQL LPAD Function can not only pad a string with spaces but also with characters supplied as arguments. Syntax and examples of PLSQL LPAD Function are mentioned below.

---

Oracle LPAD Function Syntax

SELECT LPAD(string_1,padded_length[,padding_string])
FROM table_name;

In the above PLSQL LPAD Function Syntax:-

• string_1 is the string which will be padded from the left side. The string_1 should not be NULL.
• padded_length is the number of characters in return, if the padded_length is smaller than string_1, then LPAD function will pad spaces to the left side of string_1.

---

Oracle LPAD Function – Use with SQL SELECT Statement Example

Oracle LPAD Function is used with the SQL SELECT Statement.

For example, the PLSQL LPAD Function query below returns the string padded with spaces.

SELECT LPAD('Tech', 7)
FROM dual;

The above PLSQL LPAD Function returns ‘ Tech’.

---

Oracle LPAD Function – Simple Example

The simplest example of SQL LPAD Function would be to pad a string.

For example, the SQL LPAD Function query below returns a string after padding from the left side.

SELECT LPAD('Tech', 7)
FROM dual;

The above SQL LPAD Function query returns ‘ Tech’.

Notice that the SQL LPAD Function pads the string with 3 spaces on the left side.

---

Oracle LPAD Function – Padding with Characters

Oracle LPAD Function can not only pad a string with spaces but can also pad with supplied characters.

For example, the SQL LPAD Function query returns a string after padding with ‘-‘ from the left side.

SELECT LPAD('Honey', 8, '-')
FROM dual;

The above SQL LPAD Function query returns ‘- – -Honey’.

Notice that the length of the returned string becomes 8 characters after padding with ‘-‘.

Oracle LPAD Function – Padding Exact Characters Example

If we try to pad a string with the same length as that of the string, then the Oracle LPAD Function returns the string.

For example, the SQL LPAD Function query below tries to pad a string of length 10.

SELECT LPAD('Tech Honey', 10, '-')
FROM dual;

The above SQL LPAD Function query returns ‘Tech Honey’.

Notice that there is no change in the passed and returned string as there was no scope of padding for SQL LPAD Function.