Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. Single Row Character Functions
  5. LPAD

LPAD

LPAD function in Oracle is used to format the output.

Syntax: LPAD(str1, n, str2)

LPAD returns str1, left-padded to length ‘n’ characters with the sequence of characters in str2. This function is useful for formatting the output of a query.

  • The argument n must be an integer.
  • If you do not specify str2, then the default is a single blank.
  • If str1 is longer than n, then this function returns the portion of str1 that fits in n.
SELECT LPAD('dcodeman',10,'*') FROM DUAL;
SELECT LPAD('dcodeman',10,'-') FROM DUAL;
SELECT LPAD('dcodeman',10,'*-*') FROM DUAL;
SELECT LPAD('dcodeman',2,'*') FROM DUAL;
SELECT LPAD('dcodeman',-2,'*') FROM DUAL;
SELECT LPAD('dcodeman',0,'*') FROM DUAL;

The 3rd parameter to LPAD is optional. When you don’t specify the 3rd parameter in LPAD, Oracle takes the default parameter as white space.

SELECT LPAD('dcodeman',10) FROM DUAL;

The 1st and 2nd parameter to LPAD are compulsory. If you don’t specify then Optimizer raises error i.e “ORA-00938: not enough arguments for function.

SELECT LPAD('dcodeman') FROM DUAL;
SELECT
    LPAD(LPAD(LPAD('BISWA',10,'*'),15,'#'),20,'&') Lpad_str
  FROM DUAL
Was this article helpful to you? Yes No

How can we help?