LPAD

The LPAD function returns Expression1, left-padded to length n characters with the sequence of characters in Expression2. This function is useful for formatting the output of a query.

SQL syntax

LPAD (Expression1, n [,Expression2])

Parameters

LPAD has the parameters:

Parameter	Description
Expression1	CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB operand or column to be left-padded. If Expression1 is longer than n, then LPAD returns the portion of Expression1 that fits in n.
n	Length of characters returned by the LPAD function. Must be a NUMBER integer or a value that can be implicitly converted to a NUMBER integer.
Expression2	Sequence of characters to be left-padded in Expression1. If you do not specify Expression2, the default is a single blank. Operand or column can be of type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.

Description

• If Expression1 is of type CHAR or VARCHAR2, the data type returned is VARCHAR2. If Expression1 is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2. If Expression1 is a LOB, the data type returned is the same as the LOB data type provided.

• The returned data type length is equal to n if n is a constant. Otherwise, the maximum result length of 8300 is returned.

• You can specify TT_CHAR, TT_VARCHAR, TT_NCHAR, and TT_NVARCHAR for Expression1 and Expression2. If Expression1 is of type TT_CHAR or TT_VARCHAR, the data type returned is TT_VARCHAR. If Expression1 is of type TT_NCHAR or TT_NVARCHAR, the data type returned is TT_NVARCHAR.

• For CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB data types:

  • If either Expression1 or Expression2 is NULL, the result is NULL. If n is less than or equal to 0, the result is NULL.

• For TT_CHAR, TT_VARCHAR, TT_NCHAR and TT_NVARCHAR types:

  • If either Expression1 or Expression2 is not NULL and if n is less than or equal to 0, the result is the empty string.

Examples

The following prints out the last names of the first 5 employees, left-padded with periods out to 20 characters.

Command> SELECT FIRST 5 LPAD (last_name, 20, '.') 
         FROM employees
         ORDER BY last_name;
< ................Abel >
< ................Ande >
< ............Atkinson >
< ..............Austin >
< ................Baer >
5 rows found.

Use LPAD function to left-pad the string 'LPAD Function' with string 'DEMO-ONLY' plus 2 spaces. The DEMO-ONLY string is replicated as much as it can as defined by the total characters output by the function, which is replicated three times.

Command> SELECT LPAD ('LPAD Function', 46, 'DEMO-ONLY  ') FROM dual;
< DEMO-ONLY  DEMO-ONLY  DEMO-ONLY  LPAD Function >
1 row found.

Call LPAD function with length of -1. NULL is returned.

Command> SELECT LPAD ('abc', -1, 'a')  FROM dual;
< <NULL> >
1 row found.