System numbers with leading zeros in SQL Server

In SQL Server, you may encounter situations where you need to format values ​​with leading zeros to improve display performance or data transfer efficiency. Let's solve this problem with a concrete example:

question:

We have a SQL table with employee numbers stored as character strings of length 6 (for example, '000001' to '999999'). We want to create a new table with the work number as an integer to improve data processing. How do I modify a SQL query to format the returned integer value to '000000' (with leading zeros)?

Answer:

To achieve this formatting, we can use the REPLICATE() and LEN() functions:

SELECT REPLICATE('0', 6 - LEN(EmployeeID))   EmployeeID

Here is how it works:

• REPLICATE('0', 6 - LEN(EmployeeID)) Creates a zero string with a length equal to 6 minus the length of EmployeeID.
• Then, use the operator to concatenate this zero string with EmployeeID, resulting in a formatted string with leading zeros.

For example, if EmployeeID is 7135, the query will return '007135'.

Notice:

• If the EmployeeID column is declared as INT, you can use the RTRIM() function to implicitly convert it to VARCHAR:

SELECT REPLICATE('0', 6 - LEN(RTRIM(EmployeeID)))   RTRIM(EmployeeID)

• To delete the leading zero and get the original number, use the following query:

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