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 OR NVARCHAR2 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 LPAD function. Must be a NUMBER integer or a value that can be implicitly converted to a NUMBER integer.
`Expression2`	Sequence of characters left-padded to Expression1. If you do not specify `Expression2`, then the default is a single blank. Operand or column can be of type CHAR, VARCHAR2, NCHAR, or NVARCHAR2.

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.
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, and NVARCHAR2 types:
- If either Expression1 or Expression2 is NULL, the result is NULL. If n is less than or equal to 0, then 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, then the result is the empty string.

Use LPAD function to left-pad the string 'LPAD Function' with string 'DEMO-ONLY' plus 2 spaces. Replicate string DEMO-ONLY plus 2 spaces 3 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. You see NULL is returned.

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