通过在 SQL Server 中使用前导零填充来格式化数字
我们有一个旧的 SQL 表，它被 SQL Server 2000 使用了将近 10 年。
其中，我们的员工徽章编号存储为从 000001 到 999999 的 char(6)。
我现在正在编写一个 Web 应用程序，我需要存储员工徽章编号。
在我的新表中，我可以走捷径并复制旧表，但我希望通过简单地将 int 值从 1 存储到 999999 来实现更好的数据传输、更小的大小等。
在 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。
0 对这个领域很重要，为什么要把它改成 INT？
FORMAT 函数 :-)
int 值占用的空间比 char(6) 少得多，并且每个制造的零件都会有多个这些条目。效率。
DATALENGTH()）两个字节。由于该列可能在索引中，因此您可以节省超过 2MB。加上其他列，这 2 个字节可能足以减少行的长度，足以每行节省 4KB 的页面。这将托管在移动平台上，还是您可能将注意力集中在非生产性领域？
将数字 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)
只需使用 FORMAT 函数（适用于 SQL Server 2012 或更高版本）：
SELECT FORMAT(EmployeeID, '000000')
FROM dbo.RequestItems
WHERE ID=0
参考：http://msdn.microsoft.com/en-us/library/hh213505.aspx
Format 不保留数据类型但隐式转换为 nvarchar：select sql_variant_property(50, 'BaseType'), sql_variant_property(format(50, N'00000'), 'BaseType')
FORMAT 仍然通过调用 .NET 的 IFormattable API 来实现，这在从 T-SQL 调用时会显着降低性能。请考虑改用 stackoverflow.com/a/9520709/159145 中的 REPLICATE 方法。这是 2015 年关于 FORMAT 性能不佳的文章：sqlperformance.com/2015/06/t-sql-queries/…
您可以通过这种方式更改您的程序
SELECT Right('000000' + CONVERT(NVARCHAR, EmployeeID), 6) AS EmpIDText,
EmployeeID
FROM dbo.RequestItems
WHERE ID=0
但是，这假设您的 EmployeeID 是一个数值，并且此代码将结果更改为一个字符串，我建议再次添加原始数值
编辑 当然我没有仔细阅读上面的问题。它表示该字段是 char(6)，因此 EmployeeID 不是数值。虽然这个答案本身仍然有价值，但它不是上述问题的正确答案。
'000000'真的有必要吗..？ '0' 也可以正常工作。只使用一个 0 .. 是否安全？
07135 而不是 007135。整个想法是连接到一个由所有 0 字符串转换的 EmployeedID 组成的 6 字符字符串，然后从右边缘开始获取 6 个字符。无论 ID 是什么长度，上述方法总是返回一个字符串，其中只有达到 6 个字符长度所需的零字符数
'00000'，因为 EmployeeID 将包含至少一位数字。但是 REPLICATE() 函数似乎更合适，就像在其他答案中一样
讨厌必须转换 int，这似乎要简单得多。由于只有一个字符串转换和简单的加法，因此甚至可能表现更好。
选择 RIGHT(1000000 + EmployeeId, 6) ...
只需确保“1000000”的零至少与所需大小一样多。
我将所有内容都发布在一个地方，所有内容都可以让我用 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')
从 2012 版开始，您可以使用
SELECT FORMAT(EmployeeID,'000000')
FROM dbo.RequestItems
WHERE ID=0
另一种方式，只是为了完整性。
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
尽可能干净并给出用变量替换的范围：
Select RIGHT(REPLICATE('0',6) + EmployeeID, 6) from dbo.RequestItems
WHERE ID=0
EmployeeID 列定义为 int，则 + 运算符将被视为加法而不是串联，因此将丢失零。使用 convert 可以避免这个问题。
为了在不溢出 6 个字符的情况下考虑负数...
FORMAT(EmployeeID, '000000;-00000')
SELECT replicate('0', 6 - len(employeeID)) + convert(varchar, employeeID) as employeeID
FROM dbo.RequestItems
WHERE ID=0
SELECT
cast(replace(str(EmployeeID,6),' ','0')as char(6))
FROM dbo.RequestItems
WHERE ID=0
7135 时，它给了我 7135.0。
该解决方案适用于所有 Sql 版本的带前导零的有符号/负数：
DECLARE
@n money = -3,
@length tinyint = 15,
@decimals tinyint = 0
SELECT REPLICATE('-', CHARINDEX('-', @n, 1)) + REPLACE(REPLACE(str(@n, @length, @decimals), '-', ''), ' ', '0')
最简单的总是最好的：
Select EmployeeID*1 as EmployeeID
在我的 SQL 版本中，我不能使用 REPLICATE。所以我这样做了：
SELECT
CONCAT(REPEAT('0', 6-LENGTH(emplyeeID)), emplyeeID) AS emplyeeID
FROM
dbo.RequestItems`
