MySQL - Add 0's to numbers with less than 9 digits

Question

I have thousands of entries in my database, each with 9 digits or less. I would like to do a mass update, find all rows with digits less than 9 and add 0's to make them equal 9 digits.

For example, my table looks like:

ID  |  Number  
---------------
0   |  489379
1   |  854744329
2   |  56456669

I would like to make it look like:

ID  |  Number  
---------------
0   |  000489379
1   |  854744329
2   |  056456669

How would I do this with a MySQL query?

Answer 1

The lpad function should solve your issue:

SELECT `id`, LPAD(`Number`, 9, '0')
FROM   mytable

To answer the question in the comment, this can also be applied in an update statement:

UPDATE mytable
SET    `Number` = LPAD(`Number`, 9, '0')
WHERE  LENGTH(`Number`) < 9