MariaDB LPAD() Function

The MariaDB LPAD() function left pads the given string to the specified length.

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

MariaDB LPAD() Syntax

Here is the syntax of the MariaDB LPAD() function:

LPAD(str, len[, padstr])

Parameters

str

Required. The string to be padded.

len

Required. The length to reach.

padstr

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

Return value

The LPAD() function left pads the specified string to the specified length and returns the padded string.

If len is less than the length of the original string str, str will be truncated to the length of len.

If len is negative, the LPAD() function will return NULL.

If any of the arguments is NULL, the LPAD() function will return NULL.

MariaDB LPAD() Examples

Basic example

This statement shows the basic usage of the MariaDB LPAD() function:

SELECT
    LPAD('oh', 10),
    LPAD('oh', 10, 'o'),
    LPAD('oh', 1, 'o'),
    LPAD('oh', -1, 'o'),
    LPAD('World', 15, 'Hello')\G

Output:

            LPAD('oh', 10):         oh
       LPAD('oh', 10, 'o'): oooooooooh
        LPAD('oh', 1, 'o'): o
       LPAD('oh', -1, 'o'): NULL
LPAD('World', 15, 'Hello'): HelloHelloWorld

Pad with numbers

To left pad the number 1 with 0 to reach length 10, use the following statement:

SELECT LPAD(1, 10, 0);

Output:

+----------------+
| LPAD(1, 10, 0) |
+----------------+
| 0000000001     |
+----------------+

Oracle mode

In Oracle mode, MariaDB LPAD() returns NULL instead of an empty string.

In the default default mode, the following statement returns an empty string:

SELECT LPAD('', 0);

Output:

+-------------+
| LPAD('', 0) |
+-------------+
|             |
+-------------+

Now let’s switch to Oracle mode:

SET SQL_MODE=ORACLE;

and run the code again:

SELECT LPAD('', 0);

Output:

+-------------+
| LPAD('', 0) |
+-------------+
| NULL        |
+-------------+

Conclusion

The MariaDB LPAD() function left pads the specified string with the gaven string (default a space).