在SQL Server中使用前导零填充格式化数字

Question

我们有一张旧的 SQL 表格，SQL Server 2000 使用了近 10 年。

其中，我们的员工工号存储为 char(6)，范围从000001到999999。

我正在编写一个 web 应用程序，需要存储员工工号。

在我的新表格中，我可以采取捷径并复制旧表格，但是我希望通过仅存储 1到999999 的int值来实现更好的数据传输、更小的大小等优点。

在 C# 中，我可以快速地使用以下格式设置符号将 int 值格式化为工号：

public static string GetBadgeString(int badgeNum) {
  return string.Format("{0:000000}", badgeNum);
  // alternate
  // return string.Format("{0:d6}", badgeNum);
}

我该如何修改这个简单的SQL查询语句，使得返回值也可以被格式化？

SELECT EmployeeID
FROM dbo.RequestItems
WHERE ID=0

如果EmployeeID是7135，则此查询应返回007135。

- user153923

---

由于在这个领域中，前导的 0 是有意义的，那么为什么要将它改为 INT 呢？ - Oded

SQL Server 2012将终于拥有类似于C#的FORMAT函数 :-) - marc_s

@Oden：int 值占用的空间比 char(6) 小得多，而每个制造的零件中都会有多个这样的条目。效率。 - user153923

你在遇到问题之前优化了吗？ - Oded

假设你有最多一百万个徽章号码，你认为你可以通过使用 DATALENGTH() 函数来节省两个字节的存储空间。如果该列可能在索引中使用，你可能会节省超过 2MB 的空间。而且，加上其他列的存储空间，这 2 个字节可能足以减少行长度，从而每行可节省一个 4KB 的页面。这个系统是否要部署在移动平台上？或者你是不是把注意力放在了一个无产出的领域？ - HABO

Answer 1

将数字6更改为你需要的总长度即可：

SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId

如果该列是INT类型，您可以使用RTRIM将其隐式转换为VARCHAR类型。

SELECT REPLICATE('0',6-LEN(RTRIM(EmployeeId))) + RTRIM(EmployeeId)

将这些0删除并返回“真实”数字的代码：

SELECT RIGHT(EmployeeId,(LEN(EmployeeId) - PATINDEX('%[^0]%',EmployeeId)) + 1)

- Vince Pergolizzi

---

+1 你甚至还告诉我如何去除0！让我测试一下，我会将其标记为答案。 - user153923

你不需要编写代码来删除零，只需将其赋值或转换为整数，它们就会自动被删除。 - Jimbo

只有在传入的值为字符串时才有效，如果传入整数，则输出与传入值相同的整数。 - Mordy

虽然有点老旧，但是使用 "+ convert (varchar, EmployeeID)" 而不是 "+ EmployeeID" 应该可以解决 int 类型的问题。 - Krishna Sarma

这是一个好的方法，除非EmployeeId大于6位数，否则会导致NULL结果。联系函数是答案： SELECT CONCAT(REPLICATE('0',6-LEN(CONVERT(varchar,EmployeeId))) , EmployeeId) - Mahmoud Moravej

Answer 2

只需使用FORMAT函数（适用于SQL Server 2012或更高版本）：

SELECT FORMAT(EmployeeID, '000000')
FROM dbo.RequestItems
WHERE ID=0 

参考资料： http://msdn.microsoft.com/zh-cn/library/hh213505.aspx

- EdMorte

---

这是一个老问题 - 在 SQL Server 2012 发布之前。 - user153923

不过，我希望现在这个泡泡能靠近顶部一点。 - Dave Haynes

如果有人想知道，Format 函数不保留数据类型，而是隐式转换为 nvarchar 类型：select sql_variant_property(50, 'BaseType'),sql_variant_property(format(50, N'00000'), 'BaseType')。 - Ralph

这种方法是最简单的，而且在我看来也是最好的解决方案。 - Robert Lujo

如果您正在处理大型数据集，请注意此函数。 - amd

在 SQL Server 中，FORMAT 仍然通过调用 .NET 的 IFormattable API 来实现，这会在从 T-SQL 调用时添加显著的性能损失。考虑使用 https://dev59.com/aGkw5IYBdhLWcg3w_Pbn#9520709 中的 REPLICATE 方法。这是一篇关于 FORMAT 性能差的文章，发表于 2015 年：https://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but - Dai

Answer 3

您可以按以下方式更改您的程序

SELECT Right('000000' + CONVERT(NVARCHAR, EmployeeID), 6) AS EmpIDText, 
       EmployeeID
FROM dbo.RequestItems 
WHERE ID=0 

然而，这假设你的EmployeeID是一个数字值，而这段代码将结果更改为字符串，建议再次添加原始数字值。

编辑 当然，我没有仔细阅读上面的问题。它说该字段是char(6)，所以EmployeeID不是数字值。虽然这个答案本身仍有价值，但它并不是上面问题的正确答案。

- Steve

---

我认为这是最干净的方法。谢谢。 - iheartcsharp

这个 '000000' 真的必要吗..? 只用一个 '0' 也可以正常工作。只使用一个 0 安全吗..? - shashwat

OP要求结果为6个字符长度的字符串。具体来说，如果EmployeeID是7135，则此查询应返回007135。仅使用一个“0”会返回07135而不是007135。整个想法是将转换后的EmployeedID字符串连接到由所有“0”组成的6个字符字符串中，然后从右侧开始取6个字符。无论ID的长度如何，上述方法始终返回一个字符串，其中只有所需数量的零字符以达到6个字符的长度。 - Steve

由于“EmployeeID”至少包含一个数字，因此它可能是5个零“'00000'”。但是，“REPLICATE（）”函数似乎更适合，就像其他答案中所示。 - nosklo

Answer 4

不喜欢将整数转换，这种方式看起来更简单。甚至可能运行更快，因为只有一次字符串转换和简单的加法。

选择 RIGHT(1000000 + EmployeeId, 6) ...

确保"1000000"至少有所需大小的零。

- JeffSahol

Answer 5

我把所有东西都放在一个地方，所有东西都对我起到填充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')

- Raj kumar

---

这包含了所有必要的选项。谢谢。 - kyurthich

Answer 6

从2012版本开始，您可以使用

SELECT FORMAT(EmployeeID,'000000')
FROM dbo.RequestItems
WHERE ID=0

- Hagop Simonian

---

直截了当的方法！我需要从日期中获取小时和分钟，所以我结合了concat、format和datepart函数。将其写为CONCAT_WS(':',FORMAT(datepart(hh, [Dt].[StartTime]),'00') ,FORMAT(datepart(mi, [Dt].[StartTime]),'00'))，并将其命名为StartTime。 - undefined

Answer 7

另一种方法，只是为了完整性。

DECLARE @empNumber INT = 7123
SELECT STUFF('000000', 6-LEN(@empNumber)+1, LEN(@empNumber), @empNumber)

根据您的查询，或者说

SELECT STUFF('000000', 6-LEN(EmployeeID)+1, LEN(EmployeeID), EmployeeID) 
         AS EmployeeCode
FROM dbo.RequestItems
WHERE ID=0

- Jamiec

---

@jp2code - 你需要了解一下StackOverflow是什么 - 它是像维基百科一样协作编辑的，它不再是你的问题一旦你发布了它！常常被编辑的东西包括过多的“废话”比如提前感谢和糟糕的语法/大小写。 - Jamiec

Answer 8

为了在不溢出6个字符的情况下考虑负数...

FORMAT(EmployeeID, '000000;-00000')

- Danny C

Answer 9

尽可能简洁，并给予变量替换的空间：

尽可能简洁，并给予变量替换的空间：

Select RIGHT(REPLICATE('0',6) + EmployeeID, 6) from dbo.RequestItems
WHERE ID=0

- Divanshu

---

如果“EmployeeID”列被定义为“int”，则+运算符将被视为加法而不是连接，因此零将丢失。使用“convert”将避免这个问题。 - user895964

调用REPLICATE('0',6)来避免输入'000000'只是浪费时间而已;-) - Mladen Mihajlovic

Answer 10

SELECT replicate('0', 6 - len(employeeID)) + convert(varchar, employeeID) as employeeID
FROM dbo.RequestItems 
WHERE ID=0

- Jimbo