CODEWITHSUNDEEP
mysqlintpadding
MarathonStudios
MarathonStudios

Reputation: 4331

Padding the beginning of a mysql INT field with zeroes

I have an INT field in a large MySQL database containing incremental numbers in an INT field. These numbers are currently regular autoincrement numbers (1, 2, 3) but I need to pad them to three digits with zeroes at the beginning (so I get 001, 002, 003.. 010, 011, etc).

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

Upvotes: 6

Views: 14967

Answers (2)

Vince Bowdren
Vince Bowdren

Reputation: 9208

There is no such thing as having leading zeroes on data in a numeric field in the database; the data just isn't stored that way, any more than it is stored in roman numerals. All you've got is the number three; so if you want to get the string "003" out, you've got two options:

  • Change to use a string field in the database: not recommended because you can't easily get incrementing numbers.
  • Format the number as you retrieve it from the database to add leading zeroes: better, but it has its own disadvantages - e.g. comparisons will be slower because they aren't indexed.

Upvotes: 1

Simon
Simon

Reputation: 9425

You can add a ZEROFILL attribute to the column to pad the data in the database or, when querying,

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

to retreive the data formatted as a 3 digit number

Upvotes: 17

Related Questions