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.

Answer 2

UPDATE t1 SET NUMER=LPAD(NUMER,6,'0') WHERE CHAR_LENGTH(NUMER)<=6 ; will not do what you expect since the NUMER field is an int. It will create the string '001234' from the int 1234 and then cast it back into 1234 - that is why there is no change.

Change NUMER to type int(6) zerofill and MySQL will pad it for you each time you read it.

If you really want zeros stored in the database, you have to change the type to CHAR/VARCHAR, then your LPAD update statement will work.