• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / function / Oracle/PLSQL LPAD Function

Oracle/PLSQL LPAD Function

November 26, 2012 by techhoneyadmin

Oracle PLSQL LPAD FunctionOracle 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.


Filed Under: function Tagged With: how to use LPAD Function in oracle database query, how to use LPAD Function in oracle plsql, how to use LPAD Function in oracle sql, LPAD Function in oracle plsql, LPAD Function in oracle sql, LPADPLSQL, syntax and example of LPAD Function in oracle database query, syntax and example of LPAD Function in oracle plsql, syntax and example of LPAD Function in oracle sql, using LPAD Function in oracle database query, using LPAD Function in oracle plsql, using LPAD Function in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in