LPAD前导零 [英] LPAD with leading zero

问题描述

我有带有发票编号的表.准则说数字应该有6个或更多的数字.首先尝试做:

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;  

但这并不高效，甚至还不够漂亮.我尝试了LPAD函数，但是随后出现了问题，因为function:

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.

NUMER列为INT(19)，并且已经包含以下数据:

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

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

(现在已经填充了3到7位不同长度的数据)

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

推荐答案

我认为您应该认为阅读的指南适用于发票的显示方式，而不适用于已存储在数据库中.

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.

将数字存储为INT时，它是纯数字.如果在前面加上零并再次存储，则它仍然是相同的数字.

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.

您可以如下选择NUMER字段，或为该表创建视图:

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.

我认为您对历史数据保持不变的要求是有争议的.即使是历史数据，发票001203也与发票1203相同.

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.

但是，如果您绝对必须按照描述的方式进行操作，则可以转换为VARCHAR字段.转换后的历史数据可以原样存储，任何新条目都可以填充为所需的零.但我不建议这样做.

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.

这篇关于LPAD前导零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！