6

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?

MarathonStudios
  • 3,983
  • 10
  • 40
  • 46
  • possible duplicate of [How can I set autoincrement format to 0001 in MySQL?](http://stackoverflow.com/questions/611340/how-can-i-set-autoincrement-format-to-0001-in-mysql) – Phil Ross Mar 01 '14 at 20:27

2 Answers2

16

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

Simon
  • 9,197
  • 13
  • 72
  • 115
  • 1
    +1: don't mess with the data in the database, change the data into the format you happen to need when querying the database. – andri Mar 04 '11 at 08:55
  • Agreed, always display the data differently, dont change the data in the database – Simon Mar 04 '11 at 08:56
  • I need the data to be in the three-digit format when I run the select command, which is why I wanted to change the data in the database. This should work though. – MarathonStudios Mar 04 '11 at 09:00
  • I'm using this sql: SELECT * FROM occupational_titles WHERE LPAD(CONVERT(`occupation_code`,VARCHAR(3)),3,'0') LIKE '{$category}%' LIMIT 7;, but it's throwing at error at VARCHAR. Anything I'm missing? – MarathonStudios Mar 04 '11 at 09:28
  • try puttting the LPAD in the select clause AS 'ALIAS' and then WHERE ALIAS LIKE '{category}%' – Simon Mar 04 '11 at 09:48
  • 4
    Technically, the "convert" isn't required. SELECT LPAD(`col`,3,'0') – SEoF Feb 15 '13 at 11:31
  • 2
    If you are having problems with VARCHAR, try CHAR instead. – SEoF Feb 15 '13 at 11:36
1

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.
Vince Bowdren
  • 8,326
  • 3
  • 31
  • 56