mysql - LPAD with leading zero

Question

I have table with invoice numbers. Guidelines say that numbers should have 6 or more digits. First of all tried to do:

UPDATE t1 SET NUMER=CONCAT('00000',NUMER) WHERE LENGTH(NUMER)=1;   
UPDATE t1 SET NUMER=CONCAT('0000',NUMER) WHERE LENGTH(NUMER)=2;  
UPDATE t1 SET NUMER=CONCAT('000',NUMER) WHERE LENGTH(NUMER)=3;  
UPDATE t1 SET NUMER=CONCAT('00',NUMER) WHERE LENGTH(NUMER)=4;  
UPDATE t1 SET NUMER=CONCAT('0',NUMER) WHERE LENGTH(NUMER)=5;  

but that isn't efficient, and even pretty. I tried LPAD function, but then came problem because function :

UPDATE t1 SET NUMER=LPAD(NUMER,6,'0') WHERE CHAR_LENGTH(NUMER)<=6 ;

returns ZERO rows affected. Also googled and they say that putting zero into quotes will solve problem, but didn't, any help ? It's daily import.

EDIT: Column NUMER is INT(19) and contain already data like :

NUMER
----------
1203  
12303 
123403 
1234503 
...

(it's filled with data with different length from 3 to 7 digits by now)

Answer 1

I think you should consider that the guidelines you read apply to how an invoice should be displayed, and not how it should be stored in the database.

When a number is stored as an INT, it's a pure number. If you add zeros in front and store it again, it is still the same number.

You could select the NUMER field as follows, or create a view for that table:

SELECT LPAD(NUMER,6,'0') AS NUMER
FROM ...

Or, rather than changing the data when you select it from the database, consider padding the number with zeros when you display it, and only when you display it.

I think your requirement for historical data to stay the same is a moot point. Even for historical data, an invoice numbered 001203 is the same as an invoice numbered 1203.

However, if you absolutely must do it the way you describe, then converting to a VARCHAR field may work. Converted historical data can be stored as-is, and any new entries could be padded to the required number of zeros. But I do not recommend that.