MySQL: Read out values with leading zeros

If you are talking about integer values, which of course are not coming with leading zeros from the database, you can simply store the values just as VARCHAR in MySQL.

Then, you can save as many zeros as you want directly with the values and you do not have the problem to add the zeros later.
2012-07-26 at 15:36

I am also for the LPAD solution. Otherwise, you could still declare the column as "UNSIGNED ZEROFILL". Then it should automatically work with the zeros at the beginning.

With "UNSIGNED ZEROFILL" columns, you can define the length, for example INT(5) for values with five padded digits.
2012-07-30 at 15:04

There is the beautiful MySQL command LPAD, which stands for Left PADding. I think that's exactly what you you are looking for here.

Here is a small example:

SELECT LPAD(col, 3, '0') FROM tab;

This MySQL query reads out the values from the column "col" from the table "tab" and ensures, that the missing characters to a length of 3 characters are padded with a "0".

LPAD expects 3 parameters: 

• First the column from which the values should be retrieved (here "col").
• Then how long the string should be (in this case, we want to have a length of 3 characters)
• And finally the character, with which it should be filled up (here "0", but you can also take any other character).

With this, it should work.
2012-07-29 at 09:48