Oracle RPAD

The Oracle RPAD() function returns a string right-padded with specified characters to a certain length.

Syntax

The following illustrates the syntax of the Oracle RPAD() function:

RPAD(source_string, target_length [,pad_string]);
Code language: SQL (Structured Query Language) (sql)

Arguments

The Oracle RPAD() function accepts three arguments:

1) source_string

is the string that will be padded from the right end.

2) target_length

is the length of the result string after padding.

Note that if the target_length is less than the length of the source_string, then RPAD() function will shorten down the source_string to the target_length without doing any padding.

3) pad_string

is a string to be padded.The pad_stringis optional and it defaults to a single space if you don’t specify it explicitly.

Return value

The RPAD() function returns a string with right-padded characters whose data type is either VARCHAR2 or NVARCHAR2, which depends on the data type of the source_string.

Examples

The following statement pads a string with the characters (+) from the right end:

SELECT
  RPAD( 'XYZ', 6, '+' )
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

The result is:

'XYZ+++'
Code language: SQL (Structured Query Language) (sql)

In this example, the source string is 'XYZ' which has length 3. Because the target length is 6, the RPAD() function padded 3 more characters (+) from the right end of the string 'XYZ'

See the following example.

SELECT
  RPAD( 'Testing', 4, '-' )
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

In this statement, the length of the source string 'Testing' is 7 while the target length is 4. So the RPAD() function truncates 3 characters right end of the source string which results in the following string:

'Test'
Code language: SQL (Structured Query Language) (sql)

Sometimes, you want to represent data distribution with text graphically. In this case, you can use RPAD() function to do so.

See the following cutomers table in the sample database:

The following statement uses the RPAD() function to output the bar chart of customers’ credit limits:

SELECT
  name, 
  credit_limit, 
  RPAD( '$', credit_limit / 100, '$' )
FROM
  customers
ORDER BY
  name;
Code language: SQL (Structured Query Language) (sql)

In this example, customers who have credit 100 will get one character $. For ones who have credit limits with the multiples of 100, will get the number of corresponding $ returned by the RPAD() function.

The following picture illustrates the result:

In this tutorial, you have learned how to use the Oracle RPAD() function to pad a string from the right end by specified characters to a particular length.

Was this tutorial helpful?