This tutorial page is based on examples. The Oracle RPAD function allows to format a SQL query or PL/SQL code output. The function fills the output from right side with defined characters and by default the padding character is “space”. Function Oracle Rpad syntax is following:
RPAD (<string>, <nr_of_length> [,<padding_string>]);
The following Rpad examples are going to use input string “Oracle DBA” and to understand the examples better we need to know the length of string. Online Tech Support will use Oracle Length function to get the length number. Take a look at the following Oracle Length example.
SELECT length ('Oracle DBA')
FROM dual;
The output of the last SQL query has returned number 10 and that means the string is long 10 characters. To pad the character we will set Oracle Rpad length to 11 and the output shows the “Oracle DBA” string having an extra space in the end. Oracle Rpad fills by default the length with “white-spaces”.
SELECT rpad ('Oracle DBA',11)
FROM dual;
The next Oracle Rpad example is almost the same as the last one with only this time we will define our own character and this will be a star (*). It was quite difficult to see the white-space in the end on the last SQL query output, so this time the Rpad function will reveal the padding more clear. You will see in the output a *-character in the end of the “Oracle DBA” text. The next question may raise – why is there only 1 star? We did declare the length to 11 character. The Oracle Rpad fills only the extra places on right side after the string with the Rpad character(s).
SELECT rpad ('Oracle DBA',11,'*')
FROM dual;
The third Rpad query has set length to 6 character that is less than the length of the input string has. Take a look at the query output below the “Oracle DBA” has become to “Oracle” and there is no “star” character on the right side. Oracle Rpad will truncate your input string if it doesn’t fix into the length size and there is no stars because no extra space has available. The input string doesn’t fit into 6 character.
SELECT rpad ('Oracle DBA',6,'*')
FROM dual;
The following example below is one of the ways to format the SQL query output using Oracle Rpad with Rownum. The Select statement is done using Oracle Dual table and the CONNECT BY hierarchical operator that generates 10 lines. The Oracle Rownum function gets its value dynamically and it is increasing per every next row by 1 number. Also the query is using the Oracle Rownum function in it’s Rpad length. For example the first row’s Rpad length will be 10 + 1 or 11 and the 10th row’s length is 20 characters. Take a look at the output how the right-padded extra size will be filled with *-characters.
SELECT rpad ('Oracle DBA',10+rownum,'*')
FROM dual
CONNECT BY rownum < 11;
It is quite important to know that the pad string doesn’t have to be only 1 character it can be a text or a pattern. The next SQL query below is same as the last one with only larger Rpad string “*-*-” and function Rpad will show on the first three lines only part of the pad string. The pad string will be in it’s full length starting from the fourth row.
SELECT rpad ('Oracle DBA',10+rownum,'*-*-')
FROM dual
CONNECT BY rownum < 11;
The Oracle Rpad truncating example below has Oracle Rownum amount subtracted from the initial amount 10 (10-rownum) and since we do have 10 rows the lowest Rpad row length will become to zero. Take a look what the SQL query returns with decreasing length amount that is smaller than the input string length 10.
SELECT rpad ('Oracle DBA',10-rownum,'*')
FROM dual
CONNECT BY rownum < 11;
Oracle Rpad can become quite handy to find out white-spaces in the string left side or it can be used on reports setting characters to align on right. There are many more ways to format the output using Oracle Rpad and that makes it important to know.
See Also:
Oracle Select Home