When working with MySQL, you might occasionally encounter situations where you need to pad a bunch of numbers with leading zeros.

Perhaps you have a requirement that all numbers have three digits, but in the data you’ve been supplied, the numbers range from a single digit, to two, and maybe three digits. Your requirement might be to pad all numbers with leading zeros, but only to make up for any shortfall in the three digit requirement.

The LPAD() function does exactly what you want in this scenario.

The LPAD() Function

Strictly speaking, the LPAD() function pads a string with another string. It works like this:

LPAD(str,len,padstr)

Where str is the string to pad, len is the required length of the end result, and padstr is the string that will be used to pad the other string.

Example

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

SELECT LPAD(7, 3, 0);

Result:

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

In this case, two leading zeros were added because we specified 3 as the required length.

So if we start with a two digit number, only one zero is added:

SELECT LPAD(17, 3, 0);

Result:

+----------------+
| LPAD(17, 3, 0) |
+----------------+
| 017            |
+----------------+

Non-Zero Values

The LPAD() function isn’t limited to just zeros. As mentioned, it can be used to pad any string with any other string. So you can pad a number with leading 1s, or leading letters, or other symbols if required.

SELECT LPAD(7, 10, '.');

Result:

+------------------+
| LPAD(7, 10, '.') |
+------------------+
| .........7       |
+------------------+

And because it’s actually a string function, it can be used to pad any non-numeric string. And it’s not limited to just one padding character – it can be padded with multiple characters if need be:

SELECT LPAD('Cat', 21, 'Meow! ') AS Result;

Result:

+-----------------------+
| Result                |
+-----------------------+
| Meow! Meow! Meow! Cat |
+-----------------------+