Geek Answers Handbook

Filling the beginning of the mysql INT field with zeros

I have an INT field in a large MySQL database containing incremental numbers in an INT field. These numbers are currently regular auto-increment numbers (1, 2, 3), but I need to first put them in three digits with zeros (so that I get 001, 002, 003 .. 010, 011, etc.).

What commands can I run in my database to change this column in the format I need?

+6
mysql int padding
source share
2 answers

You can add the ZEROFILL attribute to a column to populate the data in the database or, when requested,

SELECT LPAD(CONVERT(`col`,VARCHAR(3)),3,'0')

to get data formatted as a 3-digit number

+16
source share

There is no such thing as the presence of zeros in the data in a numerical field in the database; data is simply not stored in this way, no more than it is stored in Roman numerals. All you have is number three; therefore, if you want to get the string "003", you have two options:

  • Change the use of a string field in the database: not recommended because you cannot easily get increasing numbers.
  • Format the number when extracting it from the database to add leading zeros: better, but it has its drawbacks - for example, comparisons will be slower because they are not indexed.
+1
source share

More articles:


All Articles