Oracle LPAD() Function

Oracle LPAD() is a built-in function that left-pads a given string with a specified character sequence to make it a given length.

If you need to right-pad a string, use the RPAD() function.

Oracle LPAD() Syntax

Here is the syntax for the Oracle LPAD() function:

LPAD(str, len [, padstr ])

Parameters

str

Required. The string to be padded. It can be of data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

len

Required. The length that the padded string should be. It must be an integer of type NUMBER or a value that can be implicitly converted to an integer of type NUMBER.

padstr

Optional. The string used to pad the original string on the left. The default value is a space.

Return Value

The Oracle LPAD() function returns a string that is padded on the left with the given character sequence to have the specified length.

The return value of LPAD() has the same data type and character set as the str parameter.

If len is less than the length of the original string str, LPAD() returns a portion of a string of length len.

If any of the parameters is NULL, LPAD() returns NULL.

Oracle LPAD() Examples

Here are some examples that demonstrate the usage of the Oracle LPAD() function.

Basic Usage

SELECT
    LPAD('Hello', 10) Result1,
    LPAD('Hello', 10, '_') Result2
FROM dual;

Output:

RESULT1       RESULT2
_____________ _____________
     Hello    _____Hello

Padded Character Sequence

The Oracle LPAD() function allows you to pad the string with a specified character sequence:

SELECT
    LPAD('Hello', 10, 'xyz') Result
FROM dual;

Output:

RESULT
_____________
xyzxyHello

NULL Parameters

If any of the parameters is NULL, the function returns NULL.

SET NULL 'NULL';
SELECT
    LPAD(NULL, 10) Result1,
    LPAD('A', NULL) Result2,
    LPAD('A', 10, NULL) Result3,
    LPAD(NULL, NULL, NULL) Result4
FROM dual;

Output:

RESULT1    RESULT2    RESULT3    RESULT4
__________ __________ __________ __________
NULL       NULL       NULL       NULL

In this example, we used the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

The Oracle LPAD() function is a built-in function that left-pads a given string with a specified character sequence to make it a given length.