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)
To pad an integer with leading zeros to a specific length To display the integer as a decimal value, call its ToString(String) method, and pass the string "Dn" as the value of the format parameter, where n represents the minimum length of the string.
A leading zero is any 0 digit that comes before the first nonzero digit in a number string in positional notation. For example, James Bond's famous identifier, 007, has two leading zeros. When leading zeros occupy the most significant digits of an integer, they could be left blank or omitted for the same numeric value.
The format() method of String class in Java 5 is the first choice. You just need to add "%03d" to add 3 leading zeros in an Integer. Formatting instruction to String starts with "%" and 0 is the character which is used in padding. By default left padding is used, 3 is the size and d is used to print integers.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With