I’m working with an application to have the item code. Item code column is set as an INT(5) but not all the item code values are 5 digits. So I need to round off the item code based on 5-digits. If the item code is 199, it should be display as 00199.
How to make the smaller numbers into 5-digits?
Solution:
There are two methods available in MySQL to do.
1) Alter the table using the ZEROFILL in MySQL. This will add 0’s to the left of your values up to the max number of digits defined for that column:
[sql]ALTER TABLE [table_name] MODIFY COLUMN [column_name] INT(x) ZEROFILL UNSIGNED;[/sql]
Where,
table_name = table name to alter the column
column_name = desired column name to add 0’s
x = number of digits allowed in the column
Adding ZEROFILL will not cause any issue on the existing table by having foreign key value.
2) This method will helps to pad the values using LPAD() in your query, without altering the table:
[sql]
SELECT LPAD(column_name,x,’0′) FROM table_name;
[/sql]
Where,
table_name = table name to alter the column
column_name = desired column name to add 0’s
x = number of digits allowed in the column
Example:
[sql]
— Alter table to append Zero’s with specified column. Will reflect in table level
ALTER TABLE products MODIFY COLUMN item_code INT(5) ZEROFILL UNSIGNED;
— Select column value with append Zero’s. Will reflect in query level
SELECT LPAD(item_code, 5,’0′) FROM products;
[/sql]
Hopefully this will help out.