mysql format number leading zeros
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 |
+-----------------------+