How to Pad Numbers with Leading Zeros in SQL Server Queries?

SQL Server: Adding Leading Zeros to Numbers

Your legacy SQL Server 2000 database used char(6) for employee badge numbers, guaranteeing a consistent format with leading zeros. Your new web application, however, stores these as integers for improved efficiency. This presents a formatting challenge when querying the data.

Here's how to format integers with leading zeros in your SQL queries:

Solution using REPLICATE:

The REPLICATE function provides an elegant solution:

<code class="language-sql">SELECT REPLICATE('0', 6 - LEN(employeeId)) + employeeId
FROM dbo.RequestItems
WHERE ID = 0;</code>

Adjust the '6' in REPLICATE to control the total padded string length. This example maintains a six-character length.

For example, an employeeId of 7135 will return '007135'.

Handling INT Columns:

If employeeId is an INT column, use RTRIM for implicit conversion to VARCHAR:

<code class="language-sql">SELECT REPLICATE('0', 6 - LEN(RTRIM(employeeId))) + RTRIM(employeeId)
FROM dbo.RequestItems
WHERE ID = 0;</code>

Removing Leading Zeros:

To retrieve the original number (without leading zeros), use the RIGHT and PATINDEX functions:

<code class="language-sql">SELECT RIGHT(employeeId, (LEN(employeeId) - PATINDEX('%[^0]%', employeeId)) + 1)
FROM dbo.RequestItems
WHERE ID = 0;</code>

This approach offers a robust solution for managing leading zeros in both your C# code and SQL Server queries, ensuring data consistency across your application.

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