MySQL zerofill and lpad: whipping your digits into shape

I’m working with a large dataset. One of the columns is set as an INT(8) but not all of the values are 8 digits. I need to run a query that will sum values and group by the first two digits in that INT(8) column. This presents a potential problem since it may end up grouping incorrectly since I need everything to be grouped based off of 8-digit numbers. How do you make these smaller numbers into 8-digits?

Simple. There are two ways.

  1. Alter the table itself using the ZEROFILL in MySQL. This will add 0s to the left of your values up to the max number of digits defined for that column:
    • ALTER TABLE [table name] MODIFY COLUMN [column name] INT(x) ZEROFILL UNSIGNED;
    • Where table name = your table name, column name = the desired column to pad, and x is the number of digits allowed for that column
  2. Pad the values using lpad() in your query itself, without altering your table:
    • select lpad(column name,x,'0') from table name;
    • Where table name = your table name, column name = desired column to pd, and x = the number of digits to allow.
    • If your value in the DB is 332, your result will be 0332. If your value is 9, the result will be 0009.

Hopefully this will help someone facing the same problem I was. It’s also good to know that there are multiple ways of doing things.