CODEWITHSUNDEEP
phpmysqlsqlselect
steeped
steeped

Reputation: 2633

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

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?

Upvotes: 5

Views: 3176

Answers (2)

Mureinik
Mureinik

Reputation: 311163

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

Upvotes: 10

ben890
ben890

Reputation: 1133

Use a case statement

update table
set column1 = case when length(column1) = 4 then concatenate('00000', column1)....

Have an element in the case statement for every possible length of the column. Kind of manual, and there is likely an easier way, but this is one possibility.

Upvotes: 2

Related Questions