Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Padding the beginning of a mysql INT field with zeroes

Tags:

int

mysql

padding

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?

like image 545
MarathonStudios Avatar asked Mar 04 '11 08:03

MarathonStudios


1 Answers

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

like image 133
Simon Avatar answered Sep 18 '22 00:09

Simon