Formatting Numbers by padding with leading zeros in SQL Server

Question

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.

Answer 1

Try changing the number 6 to the total required length:

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

But, if the column is defined as an INT, use RTRIM to implicitly convert it to the VARCHAR like this:

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

To remove the 0s and to get back the 'real' number, you can refer to below code:

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

Learn SQL commands visit this SQL Commands Cheat Sheet.

You can master these queries and become proficient in SQL queries by enrolling in an industry-recognized SQL certification.