preserve leading zeros mysql cast unsigned code example

Example: 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 |
+-----------------------+