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?
INT columns do not store leading zeroes.
You may have to add a column that stores a string converted value with leading zeroes.
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.
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.
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