How to Format Integer Values with Leading Zeros in SQL Server?

Formatting numbers with leading zeros in SQL Server

In SQL Server, you may encounter situations where you need to format a numeric value with leading zeros to improve display or data transfer efficiency. Let’s address this with a concrete example:

Question:

We have a SQL table that contains employee numbers stored as strings of length 6 characters (for example, '000001' through '999999'). We want to create a new table in which job numbers are integers to improve data processing. How do I modify my SQL query to format the returned integer value as '000000' (with leading zeros)?

Answer:

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

<code class="language-sql">SELECT REPLICATE('0', 6 - LEN(EmployeeID)) + EmployeeID</code>

Here’s how it works:

• REPLICATE('0', 6 - LEN(EmployeeID)) Creates a zero string whose length is equal to 6 minus the length of EmployeeID.
• This zero string is then concatenated with the EmployeeID using the operator, resulting in a formatted string with leading zeros.

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

Note:

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

<code class="language-sql">SELECT REPLICATE('0', 6 - LEN(RTRIM(EmployeeID))) + RTRIM(EmployeeID)</code>

• To remove leading zeros and get the original number, use the following query:

<code class="language-sql">SELECT RIGHT(EmployeeID, (LEN(EmployeeID) - PATINDEX('%[^0]%', EmployeeID)) + 1)</code>

The above is the detailed content of How to Format Integer Values with Leading Zeros in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!