RPAD is an Oracle string function that returns a right-padded string of ‘n’ characters, padded with a padding expression.
The syntax of Oracle RPAD function is as follows:
RPAD( Expression, n, ‘padding_value’)
Here,
Expression/Column Name – Expression is a column name or string. It’s the source string that will be padded.
n (Length)- n is the number of characters we want in result. If we want the string to be 8 characters long, then we enter n=8.
Note: If the value of n is less than the no. of characters already present in the string, then RPAD will trim the Expression to ‘n’ characters. The final string always contains ‘n’ characters.
padding_value – This could be a special character, text, or even an empty string that will be used as padding. If we don’t enter any pad_value then Oracle will use single space as padding.
The data type of outcome is usually VARCHAR2 or NVARCHAR2 depending on the data type of source.
Oracle RPAD Examples
Let’s use a simple query to understand how RPAD function works. Here, we give n = 10, which is the total length of characters in a string. Padding_Value = ‘+’, the character used for right padding while the original string = ‘pqr’
SELECT RPAD( 'pqr', 10, '+' ) RPAD FROM dual;
Result:
The query returns a 10 character string with right padding as ‘+’.
Let’s see one more query where we use RPAD to trim a string of characters. Here the total number of characters we have given is 3, so the final string contains 3 characters only irrespective of the number of characters in our original string or the padding value that we have given.
SELECT RPAD( 'Batman', 3, '!' ) RPAD FROM dual;
Result:
The query returns the first 3 characters of the string.
We will be using Employee table from HR schema for this example,
Let’s use this query to right pad all rows in salary column of employees table with ‘$’ sign.
SELECT first_name, RPAD(salary,10, $) salary FROM employees;
Result:
The query returns all values in salary column of employees table padded with ‘$’ Sign.
In this tutorial, we learned how to use Oracle RPAD function.