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

问题描述

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

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

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

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.

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);
}

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

SELECT EmployeeID
FROM dbo.RequestItems
WHERE ID=0

If EmployeeID is 7135, this query should return 007135.

解决方案

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

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

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)

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屋！