How To Use Oracle RPAD Function
  Home
 

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;

online tech support online computer help computer technician computer problems computer oracle rpad oracle rpad in oracle sql rpad oracle sql rpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql

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;

online tech support online computer help computer technician computer problems computer oracle rpad oracle rpad in oracle sql rpad oracle sql rpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql

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;

online tech support online computer help computer technician computer problems computer oracle rpad oracle rpad in oracle sql rpad oracle sql rpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql

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;

online tech support online computer help computer technician computer problems computer oracle rpad oracle rpad in oracle sql rpad oracle sql rpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql

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;

online tech support online computer help computer technician computer problems computer oracle rpad oracle rpad in oracle sql rpad oracle sql rpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql

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;

online tech support online computer help computer technician computer problems computer oracle rpad oracle rpad in oracle sql rpad oracle sql rpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql

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;

online tech support online computer help computer technician computer problems computer oracle rpad oracle rpad in oracle sql rpad oracle sql rpad in oracle sql database oracle database sql length oracle length in oracle sql length oracle sql

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

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

*