How LPAD() Works in MariaDB

In MariaDB, LPAD() is a built-in string function that pads the left part of a string with a certain number of characters.

For example, you can use LPAD() to pad the left part of a string with spaces. Or you could pad a number with leading zeros.

Syntax

The syntax goes like this:

LPAD(str, len [, padstr])

Where str is the string to pad, and len is the number of characters to pad the string to. Note that this is not the amount of padding itself, rather it’s the total number of characters that the returned string will have after it’s been padded.

padstr is an optional argument that specifies which character to use for the padding.

Example

Here’s a basic example:

SELECT LPAD('Lion', 8);

Result:

+-----------------+
| LPAD('Lion', 8) |
+-----------------+
|     Lion        |
+-----------------+

In this case, we didn’t specify a third argument (for the padding character), and so a space was used. The space character is the default character.

Specify a Padding Character

Here’s an example that uses a third argument. This specifies what character to use for the padding:

SELECT LPAD('Lion', 8, '.');

Result:

+----------------------+
| LPAD('Lion', 8, '.') |
+----------------------+
| ....Lion             |
+----------------------+

The dots enable us to see the padding more clearly.

Padding Numbers

Here’s an example of padding a number with zeros:

SELECT LPAD(7, 3, 0);

Result:

+---------------+
| LPAD(7, 3, 0) |
+---------------+
| 007           |
+---------------+

Padding Smaller than the Original String

If the second argument is less than the original string, then no padding is added, and the original string is shortened to the number of characters specified:

SELECT LPAD('Lion', 2);

Result:

+-----------------+
| LPAD('Lion', 2) |
+-----------------+
| Li              |
+-----------------+

Database Examples

Here’s an example of padding the left part of the values in a database column:

SELECT 
    LPAD(PetName, 15, '.') AS "Padded Name",
    PetName AS "Original Name"
FROM Pets;

Result:

+-----------------+---------------+
| Padded Name     | Original Name |
+-----------------+---------------+
| .........Fluffy | Fluffy        |
| ..........Fetch | Fetch         |
| ........Scratch | Scratch       |
| ............Wag | Wag           |
| ..........Tweet | Tweet         |
| .........Fluffy | Fluffy        |
| ...........Bark | Bark          |
| ...........Meow | Meow          |
+-----------------+---------------+

Here’s another example that pads a price column:

SELECT 
    ProductId, 
    ProductPrice, 
    LPAD(ProductPrice, 8, 0) 
FROM Products;

Result:

+-----------+--------------+--------------------------+
| ProductId | ProductPrice | LPAD(ProductPrice, 8, 0) |
+-----------+--------------+--------------------------+
|         1 |        25.99 | 00025.99                 |
|         2 |        25.99 | 00025.99                 |
|         3 |        14.75 | 00014.75                 |
|         4 |        11.99 | 00011.99                 |
|         5 |        33.49 | 00033.49                 |
|         6 |       245.00 | 00245.00                 |
|         7 |        55.99 | 00055.99                 |
|         8 |         9.99 | 00009.99                 |
+-----------+--------------+--------------------------+

Null Arguments

If any (or all) of the arguments are null, the LPAD() function returns null:

SELECT 
    LPAD(null, 10, '.'),
    LPAD('Coffee', null, '.'),
    LPAD('Coffee', 10, null);

Result:

+---------------------+---------------------------+--------------------------+
| LPAD(null, 10, '.') | LPAD('Coffee', null, '.') | LPAD('Coffee', 10, null) |
+---------------------+---------------------------+--------------------------+
| NULL                | NULL                      | NULL                     |
+---------------------+---------------------------+--------------------------+

Oracle Mode

When not running in Oracle mode, if the result is empty (i.e. it has a length of zero) the result is an empty string.

However, when running in Oracle mode, the result is null.

Here it is in default mode (i.e. not in Oracle mode):

SELECT LPAD('', 0);

Result:

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

Now let’s switch to Oracle mode:

SET SQL_MODE=ORACLE;

And run the code again:

SELECT LPAD('', 0);

Result:

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

Missing Arguments

Calling LPAD() without at least two arguments results in an error:

SELECT LPAD('Coffee');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'LPAD'

The same thing happens when calling LPAD() without any arguments:

SELECT LPAD();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'LPAD'