通过在 SQL Server 中填充前导零来格式化数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9520661/
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
Formatting Numbers by padding with leading zeros in SQL Server
提问by jp2code
We have an old SQL table that was used by SQL Server 2000 for close to 10 years.
我们有一个旧的 SQL 表，它被 SQL Server 2000 使用了将近 10 年。
In it, our employee badge numbers are stored as char(6)from 000001to 999999.
在这里面，我们的员工徽章号码存储为char(6)从000001到999999。
I am writing a web application now, and I need to store employee badge numbers.
我现在正在编写一个 Web 应用程序，我需要存储员工徽章编号。
In my new table, I could take the short cut and copy the old table, but I am hoping for better data transfer, smaller size, etc, by simply storing the intvalues from 1to 999999.
在我的新表中，我可以走捷径并复制旧表，但我希望通过简单地存储int值1来实现更好的数据传输、更小的尺寸等999999。
In C#, I can quickly format an intvalue for the badge number using
在 C# 中，我可以使用快速格式化int徽章编号的值
public static string GetBadgeString(int badgeNum) {
return string.Format("{0:000000}", badgeNum);
// alternate
// return string.Format("{0:d6}", badgeNum);
}
How would I modify this simple SQL query to format the returned value as well?
我将如何修改这个简单的 SQL 查询来格式化返回的值？
SELECT EmployeeID
FROM dbo.RequestItems
WHERE ID=0
If EmployeeIDis 7135, this query should return 007135.
如果EmployeeID是 7135，则此查询应返回007135。
回答by Vince Pergolizzi
Change the number 6 to whatever your total length needs to be:
将数字 6 更改为您需要的总长度：
SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId
If the column is an INT, you can use RTRIM to implicitly convert it to a VARCHAR
如果列是 INT，则可以使用 RTRIM 将其隐式转换为 VARCHAR
SELECT REPLICATE('0',6-LEN(RTRIM(EmployeeId))) + RTRIM(EmployeeId)
And the code to remove these 0s and get back the 'real' number:
以及删除这些 0 并取回“真实”数字的代码：
SELECT RIGHT(EmployeeId,(LEN(EmployeeId) - PATINDEX('%[^0]%',EmployeeId)) + 1)
回答by EdMorte
Just use the FORMAT function (works on SQL Server 2012 or newer):
只需使用 FORMAT 函数（适用于 SQL Server 2012 或更新版本）：
SELECT FORMAT(EmployeeID, '000000')
FROM dbo.RequestItems
WHERE ID=0
Reference: http://msdn.microsoft.com/en-us/library/hh213505.aspx
回答by Steve
You can change your procedure in this way
您可以通过这种方式更改您的程序
SELECT Right('000000' + CONVERT(NVARCHAR, EmployeeID), 6) AS EmpIDText,
EmployeeID
FROM dbo.RequestItems
WHERE ID=0
However this assumes that your EmployeeIDis a numeric value and this code change the result to a string, I suggest to add again the original numeric value
但是，这假设您EmployeeID是一个数值并且此代码将结果更改为字符串，我建议再次添加原始数值
EDITOf course I have not read carefully the question above. It says that the field is a char(6)so EmployeeID is not a numeric value. While this answer has still a value per se, it is not the correct answer to the question above.
编辑当然我没有仔细阅读上面的问题。它说该字段是一个char(6)所以 EmployeeID 不是一个数值。虽然这个答案本身仍然有价值，但它不是上述问题的正确答案。
回答by JeffSahol
Hated having to CONVERT the int, and this seems much simpler. Might even perform better since there's only one string conversion and simple addition.
讨厌必须转换 int，这似乎更简单。甚至可能表现得更好，因为只有一个字符串转换和简单的加法。
select RIGHT(1000000 + EmployeeId, 6) ...
选择RIGHT(1000000 + EmployeeId, 6) ...
Just make sure the "1000000" has at least as many zeros as the size needed.
只需确保“1000000”至少具有与所需大小一样多的零。
回答by Raj kumar
I am posting all at one place, all works for me to pad with 4 leading zero :)
我在一个地方发布所有内容，所有内容都适合我用 4 个前导零填充:)
declare @number int = 1;
print right('0000' + cast(@number as varchar(4)) , 4)
print right('0000' + convert(varchar(4), @number) , 4)
print right(replicate('0',4) + convert(varchar(4), @number) , 4)
print cast(replace(str(@number,4),' ','0')as char(4))
print format(@number,'0000')
回答by Jamiec
Another way, just for completeness.
另一种方式，只是为了完整性。
DECLARE @empNumber INT = 7123
SELECT STUFF('000000', 6-LEN(@empNumber)+1, LEN(@empNumber), @empNumber)
Or, as per your query
或者，根据您的查询
SELECT STUFF('000000', 6-LEN(EmployeeID)+1, LEN(EmployeeID), EmployeeID)
AS EmployeeCode
FROM dbo.RequestItems
WHERE ID=0
回答by Hagop Simonian
From version 2012 and on you can use
从 2012 版开始，您可以使用
SELECT FORMAT(EmployeeID,'000000')
FROM dbo.RequestItems
WHERE ID=0
回答by Divanshu
As clean as it could get and give scope of replacing with variables:
尽可能干净并提供替换变量的范围：
Select RIGHT(REPLICATE('0',6) + EmployeeID, 6) from dbo.RequestItems
WHERE ID=0
回答by Jimbo
SELECT replicate('0', 6 - len(employeeID)) + convert(varchar, employeeID) as employeeID
FROM dbo.RequestItems
WHERE ID=0
回答by user1227804
SELECT
cast(replace(str(EmployeeID,6),' ','0')as char(6))
FROM dbo.RequestItems
WHERE ID=0
