MariaDB has an LPAD() function that allows us to pad the left part of a string or number with our chosen character or series of characters.

We can use this function on numeric values in order to add leading zeros.

Example

Here’s an example to demonstrate:

SELECT LPAD(7, 3, 0);

Result:

007

The first argument is the number, the second is the number of characters we want the output to have, and the third argument is an optional argument that specifies which character we want to pad the number with. If we omit this third argument, the number is padded with spaces.

Here’s another example:

SELECT LPAD(7.45, 5, 0);

Result:

07.45

And another:

SELECT LPAD(747, 20, 0);

Result:

00000000000000000747

The LPAD() function isn’t limited to just zeros. We can pad the number with any character we want:

SELECT LPAD(7, 2, 'G');

Result:

G7

And we can use more than one character:

SELECT LPAD(7, 4, 'G00');

Result:

G007

The whole string is repeated if required:

SELECT LPAD(7, 20, 'G00');

Result:

G00G00G00G00G00G00G7