RPAD() Function in Oracle

by

In Oracle, the RPAD() function allows you to pad the right 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:

RPAD(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, right-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 RPAD('Cat', 25)
FROM DUAL;

Result:

              RPAD('CAT',25) 
____________________________ 
Cat

It’s not easy to see the effect of this example, because we padded the string with the default character (a space), but it did have the effect of pushing out the width of the column.

Below is another example that demonstrates the effect better:

SELECT RPAD('Cat', 15) || 'House'
FROM DUAL;

Result:

   RPAD('CAT',15)||'HOUSE' 
__________________________ 
Cat            House

Concatenating the two strings reveals the amount of padding that was applied to the right part of the leftmost string.

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

Specify a Character

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

SELECT RPAD('Cat', 7, '!')
FROM DUAL;

Result:

   RPAD('CAT',7,'!') 
____________________ 
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 RPAD('Cat', 2)
FROM DUAL;

Result:

   RPAD('CAT',2) 
________________ 
Ca

Database Example

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

SELECT 
    country_name,
    RPAD(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 
    RPAD(null, 3),
    RPAD('Cat', null),
    RPAD('Cat', 3, null)
FROM DUAL;

Result:

   RPAD(NULL,3)    RPAD('CAT',NULL)    RPAD('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 RPAD() without passing any arguments returns an error:

SELECT RPAD()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT RPAD()
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 RPAD('Cat', 1, '>', 2)
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT RPAD('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: