通过在 SQL Server 中填充前导零来格式化数字 [英] Formatting Numbers by padding with leading zeros in SQL Server

问题描述

我们有一个 SQL Server 2000 使用了近 10 年的旧 SQL 表.

We have an old SQL table that was used by SQL Server 2000 for close to 10 years.

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

In it, our employee badge numbers are stored as char(6) from 000001 to 999999.

我现在正在编写一个 Web 应用程序，我需要存储员工证号.

I am writing a web application now, and I need to store employee badge numbers.

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

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 int values from 1 to 999999.

在 C# 中，我可以使用

In C#, I can quickly format an int value for the badge number using

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

我将如何修改这个简单的 SQL 查询以格式化返回值?

How would I modify this simple SQL query to format the returned value as well?

SELECT EmployeeID
FROM dbo.RequestItems
WHERE ID=0

如果EmployeeID是7135，这个查询应该返回007135.

If EmployeeID is 7135, this query should return 007135.

推荐答案

将数字 6 更改为所需的总长度:

Change the number 6 to whatever your total length needs to be:

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

如果该列是一个INT，你可以使用RTRIM将它隐式转换为一个VARCHAR

If the column is an INT, you can use RTRIM to implicitly convert it to a VARCHAR

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

以及删除这些 0 并取回真实"数字的代码:

And the code to remove these 0s and get back the 'real' number:

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

这篇关于通过在 SQL Server 中填充前导零来格式化数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！