LPAD() Function in Oracle

In Oracle, the LPAD() function allows you to pad the left part of a string with a certain character, to a specified number of characters.

The way it works is, you specify how long the resulting string should be. If the original string is shorter, the padding character fills in the remaining space.

Syntax

The syntax goes like this:

LPAD(expr1, n [, expr2 ])

Where:

• expr1 and expr2 can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. 
• n is a NUMBER integer or a value that can be implicitly converted to a NUMBER integer. 

The function returns expr1, left-padded to length n characters with the sequence of characters in expr2.

If expr2 is omitted, the padding character is a single blank.

Example

Here’s a simple example to demonstrate:

SELECT LPAD('Cat', 5)
FROM DUAL;

Result:

   LPAD('CAT',5) 
________________ 
  Cat           

Note that the number provided is the total width of the resulting string – not the amount of padding.

Here it is again when compared to the original (non-padded) string:

SELECT LPAD('Cat', 5)
FROM DUAL
UNION ALL
SELECT 'Cat'
FROM DUAL;

Result:

   LPAD('CAT',5) 
________________ 
  Cat            
Cat             

Specify a Character

In this example I specify a character to use for the padding:

SELECT LPAD('Cat', 5, '>')
FROM DUAL;

Result:

   LPAD('CAT',5,'>') 
____________________ 
>>Cat               

Padding Smaller than the Original String

If the second argument is less than the original string, then no padding is added, and the original string is shortened to the number of characters specified:

SELECT LPAD('Cat', 2)
FROM DUAL;

Result:

   LPAD('CAT',2) 
________________ 
Ca              

Database Example

Here’s an example of padding the left part of the values in a database column:

SELECT 
    country_name,
    LPAD(country_name, 12, '.') AS Padded
FROM countries
FETCH FIRST 5 ROWS ONLY;

Result:

   COUNTRY_NAME          PADDED 
_______________ _______________ 
Argentina       ...Argentina    
Australia       ...Australia    
Belgium         .....Belgium    
Brazil          ......Brazil    
Canada          ......Canada    

Null Values

If any of the arguments is null the result is null:

SET NULL 'null';

SELECT 
    LPAD(null, 3),
    LPAD('Cat', null),
    LPAD('Cat', 3, null)
FROM DUAL;

Result:

   LPAD(NULL,3)    LPAD('CAT',NULL)    LPAD('CAT',3,NULL) 
_______________ ___________________ _____________________ 
null            null                null                  

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

Incorrect Argument Count

Calling LPAD() without passing any arguments returns an error:

SELECT LPAD()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LPAD()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:

And passing the wrong number of arguments results in an error:

SELECT LPAD('Cat', 1, '>', 2)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LPAD('Cat', 1, '>', 2)
FROM DUAL
Error at Command Line : 1 Column : 28
Error report -
SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"
*Cause:    
*Action: