00 Votes

MySQL: Read out values with leading zeros

Question by Sledge | 2012-07-25 at 19:50

I want to retrieve some values from a column in MySQL with leading zeros. Is there a MySQL command available for that?

I would prefer that the values come from MySQL directly with the leading zeros, so that I do no have to add some PHP lines of code to post-format the values or add the zeros in another way manually.

ReplyPositiveNegativeDateVotes
Avatar
00 Votes

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

ReplyPositive Negative
Avatar
00 Votes

I do not recommend to do that. The other solutions are a lot better than this VARCHAR solution. If you have integer values, you should actually save the values also in a integer field.

First, a VARCHAR field consumes unnecessary much space. Secondly, the processing (for example, the addition of values and so on) with an integer column is much easier than with string data types.
2012-07-27 at 19:28

Positive Negative
Reply
Avatar
00 Votes

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

ReplyPositive Negative
Avatar
4Best Answer4 Votes

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

ReplyPositive Negative
Reply

Related Topics

PHPExcel: Leading Zeros

Question | 1 Answer

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.