LPAD and RPAD

LPAD (left pad) and RPAD (right pad) are SQL functions used to add padding characters to the left or right side of a string up to a given length. The default padding character is a space. If the string's length is greater than the required length, it will be trimmed (excess characters will be removed).

Examples

SQL> SELECT amount, LPAD(amount, 12, '*') result
  2  FROM TEST;
AMOUNT    RESULT
--------- ------------------------------
12,345    ******12,345
12,345.67 ***12,345.67

Warning

Take care that the buffer limit of these functions is 4000 BYTES (not CHAR). If you are working with a multibyte or varying-size character set you may encounter some weird things.

On single byte character set (WE8MSWIN1252):

SQL> select length(lpad('é',4000,'é')) from dual;
LENGTH(LPAD('É',4000,'É'))
--------------------------
                      4000

On multibyte (AL32UTF8):

SQL> select length(lpad('é',4000,'é')) from dual;
LENGTH(LPAD('É',4000,'É'))
--------------------------
                      2000

Only the first 2000 characters are taken into account because 'é' is coded with 2 bytes.