How to left pad numbers with zeros in MariaDB

In MariaDB, the LPAD() function is used to left pad strings or numbers. When you want to leftpad a given number with leading zeros, you can use the LPAD() function.

The MariaDB LPAD() function accepts 3 parameters:

1. The first argument is a string or number to be left padded.
2. The second parameter is the length it has after padding.
3. The third parameter is the character/string to fill. If it is omitted, it defaults to a space.

This article mainly discusses left padding a number with zeros to make it to a specified length.

Example 1: Leading zeros

The following statement will pad the number 9 with 0 to the left to make it’s length to 5:

SELECT LPAD(9, 5, 0);

Output:

+---------------+
| LPAD(9, 5, 0) |
+---------------+
| 00009         |
+---------------+

Of course, you can also specify a number with decimal places, as follows:

SELECT LPAD(1.23, 8, 0);

Output:

+------------------+
| LPAD(1.23, 8, 0) |
+------------------+
| 00001.23         |
+------------------+

Example 2: Leading characters

You can specify any leading characters you want, for example #:

SELECT LPAD(123, 5, '#');

Output:

+-------------------+
| LPAD(123, 5, '#') |
+-------------------+
| ##123             |
+-------------------+

Example 3: leading string

LPAD also supports you to specify a leading string, and it will be filled to the left of the number according to the actual situation.

For example, the following statement will left pad 123 with #00 to make it’s length to 10:

SELECT LPAD(123, 10, '#00');

Output:

+----------------------+
| LPAD(123, 10, '#00') |
+----------------------+
| #00#00#123           |
+----------------------+

Conclusion

This article discusses left padding numbers with 0 using the LPAD function.