通过在 SQLServer 中使用前导零填充来设置数字的格式

我们有一个被 SQLServer2000使用了近10年的旧 SQL 表。

在其中，我们的员工徽章号码存储为 char(6)从 000001到 999999。

我现在正在编写一个网络应用程序，我需要存储员工徽章号码。

在我的新表中，我可以走捷径并复制旧表，但是我希望通过简单地将 int值从 1存储到 999999来获得更好的数据传输、更小的尺寸等。

在 C # 中，我可以使用

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。

453204 次浏览
小开
最佳答案

把数字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)
小开

您可以用这种方式更改程序

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

但是这里假设你的 EmployeeID是一个数值，并且这个代码将结果改为一个字符串，我建议再次添加原来的数值

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

小开 
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
小开

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

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
小开

我讨厌转换整型，这看起来简单多了。甚至可能性能更好，因为只有一个字符串转换和简单的添加。

选择 右边(1000000 + EmployeeId，6) ..。

只要确保“1000000”至少有足够多的零作为所需的大小。

小开

只需使用 FORMAT 函数(适用于 SQLServer2012或更新版本) :

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

Reference: http://msdn.microsoft.com/en-us/library/hh213505.aspx

小开

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

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

我张贴所有在一个地方，所有工作为我垫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')
小开

该解决方案适用于所有 Sql 版本的前导零的有符号/负数:

DECLARE
@n money = -3,
@length tinyint = 15,
@decimals tinyint = 0


SELECT REPLICATE('-', CHARINDEX('-', @n, 1)) + REPLACE(REPLACE(str(@n, @length, @decimals), '-', ''), ' ', '0')
小开

在我的 SQL 版本中，我不能使用 REPLICATE，所以我这样做了:

SELECT
CONCAT(REPEAT('0', 6-LENGTH(emplyeeID)), emplyeeID) AS emplyeeID
FROM
dbo.RequestItems`
小开

最简单的总是最好的:

Select EmployeeID*1 as EmployeeID
小开

要解释负数而不溢出6个字符..。

FORMAT(EmployeeID, '000000;-00000')

提交答案