Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

LPAD with leading zero

Tags:

mysql

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)

like image 658
jaczes Avatar asked Jul 12 '13 10:07

jaczes


People also ask

How can I pad a value with leading zeros?

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.

What is a leading zero example?

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.

How do you add leading zeros to a string?

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.


2 Answers

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.

like image 168
Gustav Bertram Avatar answered Sep 29 '22 13:09

Gustav Bertram


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.

like image 41
Erik Ekman Avatar answered Sep 29 '22 12:09

Erik Ekman