Adding a leading zero to some values in column in MySQL

Using conditional logic with LPAD

When you want to sparingly apply leading zeros or pad only specific values, combine the LPAD with an IF function. This allows you to determine if padding is needed:

UPDATE your_table
SET your_column = IF(CHAR_LENGTH(your_column) < 4, LPAD(your_column, 4, '0'), your_column);


This method ensures already apt values stay the same, no unnecessary zeros there!

Numerical vs varchar: How to deal with types

Numeric types can automatically drop leading zeros. If you're keen on holding onto those zeros, change your column to a VARCHAR type:

ALTER TABLE your_table 
MODIFY your_column VARCHAR(255);


Do this, especially when your data is on the import queue and you wish to preserve the leading zeros.

The ZEROFILL and CHAR alternative

When you convert your field back to a numeric type and wish to keep the leading zeros without using LPAD, go with ZEROFILL:

ALTER TABLE your_table 
CHANGE your_column your_column INT(4) ZEROFILL;


To get a fixed length of characters filled with zeros, set your column to CHAR:

ALTER TABLE your_table 
MODIFY your_column CHAR(4);


Isn't it great to variate and keep your 🦆 in a row?

The less-complicated CONCAT method

When LPAD feels like overkill (We all love simplicity, don't we? 😅), just stick to CONCAT:

UPDATE your_table
SET your_column = CONCAT('0', your_column) WHERE LENGTH(your_column) = 3;


But hey, don't forget to ensure your value length fits the ticket to prevent data truncation. You don't want your data looking like a badly shorn sheep, do you?

Potential pitfalls and verification

Don't sprint yet, hang on! Validate your column type and test your query on a subset of your data. Use a SELECT command for a sneak peek of the LPAD effect:

SELECT your_column, LPAD(your_column, 4, '0') as padded_column
FROM your_table
WHERE CHAR_LENGTH(your_column) < 4;


Avoid slice and dice and ensure whole data integrity. Keep an eye on the max row size limits and remember: wise decisions prevent data monstrosities!