如何在 SQL Server 中插入前导零
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15237100/
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 insert leading zero in SQL Server
提问by Matthew Sarmiento
This is the code for selecting with leading zeros:
这是选择前导零的代码：
SELECT RIGHT('00000'+ CONVERT(VARCHAR,Asset_No),6)
FROM schemaAsset.SystemDefined
How can I insert a value to the column Asset_no with leading zeros?
如何向带有前导零的列 Asset_no 插入值？
Example is I am only inserting "1" to the column Asset_no, but in the code, it will insert "000001". And also when I insert two digits like 26, it will insert as "000026". The length does not change. Is this possible in SQL Server?
示例是我只在 Asset_no 列中插入“1”，但在代码中，它会插入“000001”。而且当我插入两个数字（如 26）时，它会插入为“000026”。长度不变。这在 SQL Server 中可能吗？
回答by Aaron Bertrand
One way to do this is with an INSTEAD OFtrigger.
一种方法是使用INSTEAD OF触发器。
USE tempdb;
GO
CREATE TABLE dbo.whatever
(
Asset_No VARCHAR(6)
);
GO
CREATE TRIGGER dbo.fix_whatever
ON dbo.whatever
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.whatever(Asset_No /*, other columns */)
SELECT RIGHT('000000' + CONVERT(VARCHAR(6), Asset_No), 6)
/*, other columns */
FROM inserted;
END
GO
INSERT dbo.whatever(Asset_No) SELECT '22';
GO
SELECT Asset_No FROM dbo.whatever;
GO
Results:
结果：
Asset_No
--------
000022
回答by Trey Mack
If you want to do the padding in SQL Server, you can do something like this with your INSERT statement:
如果要在 SQL Server 中进行填充，可以使用 INSERT 语句执行以下操作：
DECLARE @Asset_no INT = 12;
INSERT INTO schemaAsset.SystemDefined (Asset_no)
SELECT RIGHT('00000'+ CONVERT(VARCHAR(6),@Asset_No),6)
回答by vadorian
The script below allows you to populate a column with numbers for every row in the table. This will give you numbers starting at 2000. The first entry would be 0002000.
下面的脚本允许您用表格中每一行的数字填充一列。这将为您提供从 2000 开始的数字。第一个条目是 0002000。
Declare @id varchar(15)
set @id = '1999'
update sometable
set @id = id = replace(STR(@id + 1,7),' ', '0')
go
回答by WSimpson
For Oracle:
对于甲骨文：
lpad(cast(NUMERIC_VAR as VARCHAR2(5 BYTE)),5,'00000')
lpad(cast(NUMERIC_VAR as VARCHAR2(5 BYTE)),5,'00000')
