Search notes:

Oracle functions: RPAD and LPAD

LPAD ('string', n [, 'string_pad')
RPAD ('string', n [, 'string_pad')
'string' is left/right padded to length n using 'string_pad'.
If string_pad is omitted, a space will be used as default.
The returned string is never longer than 4000 characters if max_string_size is set to standard or 32767 characters if set to extended.
The second parameter (n) to lpad or rpad determines the resulting length. If it is longer than n, string will be truncated:
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
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<
Same thing as above, 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         <
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

Pretty printing output

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;
The query returns:
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;
This query returns:
apple      -       pear
grapefruit -      mango
orange     -     banana
lemon      - watermelon

Maximum returned length

The maximum length returned by rpad or lpad is determined by the value of max_string_size. If the value is standard, this length is 4000 otherwise 32767.
The following query returns 4000 (or 32767), rather than throwing an error:
select
   length(rpad('*', 1234567890, '*')) len
from
   dual;

Side effects of empty strings

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

See also

Functions related to text

Index