views:

114

answers:

5

Hi there, I've got a large table (~10,000) and I need one column to take up exactly three spaces. It almost always only takes up one space, but I need the other two spaces to be filled in with zeros (it's an integer column). is there a function for that?

A: 

INT columns do not store leading zeroes.

You may have to add a column that stores a string converted value with leading zeroes.

Raj More
+2  A: 

Add ZEROFILL attribute to the int column.

Tomas
Its described here: (3rd paragraph below the table):http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
Tomas
Danke Schoen, Muito Obrigado, and thanks :-)
btelles
+4  A: 

Why would you do this? If it is because you want some report that queries the table to display one digit values as 3 digits, add the padding zeroes when you display it. Don't mix display information into your data.

Brian Schroth
Wish I could upvote this more than once.
Eric Petroelje
Why on earth would you take the time to assume I don't know how to structure data, and then take the time to write a smart-a** answer? I'm not here to debate data structure theory, I'm here to get a question answered.
btelles
sorry, I'm not a mind reader. A lot of people ask questions who don't know how to structure data, and it seemed like a likely explanation for why someone would want this. No need to get offended, you can just skip this answer and go on to the ones that tell you to use ZEROFILL.
Brian Schroth
A: 

If you have control over the definition of the table in question, you can use MySQL's zerofill attribute. It lets you define a numeric column that is padded with zeroes.

If you don't have this control (and what you really ought to be doing anyway), add the padding as you query the values from the table:

SELECT LPAD(CONVERT(IntColumn,VARCHAR(3)),3,'0')

That way the integer is still stored as a normal integer like it should be, but from the point of view of whatever is consuming this query, the integers are padded.

Welbog
A: 

To add the padding when you display the data with select, you can use LPAD:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_lpad

Zach Wily