René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

RPAD and LPAD [Oracle SQL]

lpad ('string', n [, 'string_pad')
rpad ('string', n [, 'string_pad')
string is left padded to length n with string_pad. If string_pad is ommited, a space will be used as default
rpad is similar, but pads right instead of left.

Demonstration 1

The second parameter (n) to lpad or rpad determines the resulting length. If string is longer than n, string will be cut:
select 
  lpad('string with 25 characters', 20) lpad,
  rpad('string with 25 characters', 20) rpad
from dual;
LPAD                 RPAD
-------------------- --------------------
string with 25 chara string with 25 chara

Demonstration 2

The literal string is lpadded to all lengths between 1 and 15. The < shows where the string is cut. As soon as the second parameter becomes larger than the length of the first parameter, lpadding occurs. The default is space, so spaces are prepended to string:
begin

  for i in 1 .. 15 loop
    dbms_output.put_line(
      lpad('string', i) || '<'
    );
  end loop;

end;
/
s<
st<
str<
stri<
strin<
string<
 string<
  string<
   string<
    string<
     string<
      string<
       string<
        string<
         string<

Demonstration 3

Same thing as in demonstration 2, but with rpad instead of lpad:
begin

  for i in 1 .. 15 loop
    dbms_output.put_line(
      rpad('string', i) || '<'
    );
  end loop;

end;
/

s<
st<
str<
stri<
strin<
string<
string <
string  <
string   <
string    <
string     <
string      <
string       <
string        <
string         <

Demonstration 4

This demonstration is again similar to demonstration 2, but it uses a non-default padding string (.,_).
begin

  for i in 1 .. 15 loop
    dbms_output.put_line(
      lpad('string', i, '.,_') 
    );
  end loop;

end;
/
s
st
str
stri
strin
string
.string
.,string
.,_string
.,_.string
.,_.,string
.,_.,_string
.,_.,_.string
.,_.,_.,string
.,_.,_.,_string

Demonstration 5

This demonstration shows how to pretty print some output.
create table rlpad_tbl (
  col_1 varchar2(10),
  col_2 varchar2(10)
);

insert into rlpad_tbl values ('apple',     'pear'      );
insert into rlpad_tbl values ('grapefruit','mango'     );
insert into rlpad_tbl values ('orange',    'banana'    );
insert into rlpad_tbl values ('lemon',     'watermelon');
Now, the values of col_1 and col_2 are selected and concatenated:
select col_1 || ' - ' || col_2 from rlpad_tbl;
This results in:
apple - pear
grapefruit - mango
orange - banana
lemon - watermelon
The length of the values of col_1 varies. This is because col_1 is of type varchar2. Only the relevant (or used) portion of its capacity is printed.
It might be desired to neatly align the parts of the concatenated string. rpad and lpad comes to rescue:
select
  rpad(col_1, 10) || ' - ' ||
  lpad(col_2, 10)
from
  rlpad_tbl;
apple      -       pear
grapefruit -      mango
orange     -     banana
lemon      - watermelon

Empty string side effects

Oracle treats an empty string as null. And nulls are always a bit special within Oracle. Particularly, rpad('', n) and lpad('', n) returns null.