SQL 如何在访问表中的文本字段前添加零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3281540/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add zeros in front of a text field in an access table
提问by tksy
I have an access table with a text field. It has alpha numeric values. But i want all values to be minimum 3 digit. so i want to add zeroes in front of all single or two digit values
我有一个带有文本字段的访问表。它具有字母数字值。但我希望所有值都至少为 3 位数。所以我想在所有单个或两个数字值前添加零
.
.
5 must become 005
89 must become 089
how do i write a query to update all values in the table.
我如何编写查询来更新表中的所有值。
thanks tksy
谢谢 tksy
回答by mdma
The key is to add as many zeros as needed to take the length up to 3.
关键是根据需要添加尽可能多的零以使长度达到 3。
UPDATE yourTable
SET YourField = LEFT("00", 3-LEN(YourField)) + YourField
WHERE LEN(YourField)<3 AND Len(YourField)>0
回答by Martin Smith
update tbl
set Col1 = RIGHT("000" + Col1,3)
where len(Col1) < 3
回答by onedaywhen
While its usually important to try to do as much as possible in one SQL statement (to help the optimizer for OLTP applications etc), this appears to be a data corruption scenario so I assume a one-off data scrubbing exercise is all that's required. And when you consider there's only two cases ("single or two digit values") there's no real harm in using two UPDATEstatements e.g.
虽然尝试在一条 SQL 语句中尽可能多地执行操作通常很重要（以帮助优化 OLTP 应用程序等），但这似乎是一种数据损坏情况，因此我认为只需要一次性数据清理练习即可。并且当您考虑只有两种情况（“单个或两位数字值”）时，使用两个UPDATE语句并没有真正的危害，例如
UPDATE Test1
SET text_col = '0' + text_col
WHERE text_col ALIKE '[0-9][0-9]';
UPDATE Test1
SET text_col = '00' + text_col
WHERE text_col ALIKE '[0-9]';
HOWEVER, the most important part of the exercise is to apply data constraints to ensure the data corruption doesn't reoccur e.g. (ANSI-92 SQL Mode syntax):
然而，练习中最重要的部分是应用数据约束以确保数据损坏不会再次发生，例如（ANSI-92 SQL 模式语法）：
ALTER TABLE Test1 ADD
CONSTRAINT text_col__numeric__at_least_three_digits
CHECK (
text_col NOT ALIKE '[0-9]'
AND text_col NOT ALIKE '[0-9][0-9]'
);
回答by KPV
Create --> Query Design --> Update Query
创建 --> 查询设计 --> 更新查询
Select the variable you want to update, then in the update to field, type:
选择要更新的变量，然后在更新到字段中键入：
Format([variable name],"0000000")
格式([变量名],"0000000")
I used 7 zeros above because I wanted the to fill in zeros in the beginning of a number string up to 7 places total.
我在上面使用了 7 个零，因为我希望在数字字符串的开头填充最多 7 个位置的零。
Note that this can be used only for a text field.
请注意，这只能用于文本字段。
回答by JesseJ
ACCESS
使用权
SELECT FORMAT(startTable.numField,"00#") AS txtField
FROM startTable
WHERE startTable.numField IS NOT NULL;
...
...
UPDATE destinationTable
SET destinationTable.txtField = FORMAT(startTable.numField,"00#")
WHERE startTable.numField IS NOT NULL;
...
...
INSERT INTO destinationTable ( txtField )
SELECT FORMAT(startTable.numField,"00#") AS txtField
FROM startTable
WHERE startTable.numField IS NOT NULL;
SQL
SQL
SELECT RIGHT('000' + CAST(numField AS VARCHAR(3)), 3) AS txtField
FROM startTable
WHERE startTable.numField IS NOT NULL
...
...
UPDATE destinationTable
SET destinationTable.txtField = RIGHT('000' + CAST(startTable.numField AS VARCHAR(3)), 3)
WHERE startTable.numField IS NOT NULL
...
...
INSERT INTO destinationTable ( txtField )
SELECT RIGHT('000' + CAST(numField AS VARCHAR(3)), 3) AS txtField
FROM startTable
WHERE startTable.numField IS NOT NULL
回答by MJB
In general, you can prefix 3 zeros to the front and then substring out the last 3 digits from the right. Something like right("000" + val, 3)if those were real function.
通常，您可以在前面添加 3 个零前缀，然后从右侧将最后 3 个数字分串出来。就像right("000" + val, 3)那些是真正的功能一样。
I think in Access it is MID("000" & val, 3, 3)
我认为在 Access 中它是 MID("000" & val, 3, 3)
