Formatting numbers by padding with leading zeros in sql server

1.6K Asked by DavidWHITE in QA Testing , Asked on Jul 10, 2021

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

How to add leading zeros in sql?

Answered by Hellen Cargill

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)

Your Answer

Answers (2)

Ranjana

If you need to format numbers with leading zeros in SQL Server, there are a few ways to do it depending on your requirements

1 Using FORMAT Function SQL Server 2012 and Later

The simplest way to add leading zeros is by using the FORMAT function

  SELECT FORMAT(5, '0000') AS PaddedNumber

If the number is 5, it will be formatted as 0005
The number of zeros in the format string determines the total length

2 Using RIGHT and REPLICATE Functions

For older SQL Server versions, you can use RIGHT and REPLICATE

  SELECT RIGHT('0000' + CAST(5 AS VARCHAR), 4) AS PaddedNumber

This ensures the number is always four digits long
The RIGHT function takes the last four characters after adding zeros

Another way using REPLICATE

  SELECT RIGHT(REPLICATE('0', 4) + CAST(5 AS VARCHAR), 4) AS PaddedNumber

REPLICATE('0', 4) generates 0000
RIGHT ensures the final length is exactly four characters

3 Using FORMAT with Leading Zeros for Decimal Numbers

If dealing with decimal numbers

  SELECT FORMAT(5.2, '0000.00') AS PaddedNumber

This ensures the output has four digits before and two digits after the decimal

4 Final Thoughts

Use FORMAT for a simple and modern approach SQL Server 2012 and later
Use RIGHT and REPLICATE for older versions
Always make sure the length is correct based on your requirements

2 Weeks

Ranjana

In SQL Server, you can format numbers by padding them with leading zeros using the FORMAT function or the RIGHT function in combination with REPLICATE. Here are two methods to achieve this:

Method 1: Using FORMAT Function

The FORMAT function is available from SQL Server 2012 onwards and can be used to format numbers easily.

  SELECT FORMAT(123, '0000') AS PaddedNumberThis will output 0123.Method 2: Using RIGHT and REPLICATE

If you're using a version of SQL Server prior to 2012 or prefer not to use the FORMAT function, you can use the RIGHT and REPLICATE functions.

  SELECT RIGHT(REPLICATE('0', 4) + CAST(123 AS VARCHAR(10)), 4) AS PaddedNumberThis will also output 0123.

Explanation:

  REPLICATE('0', 4) creates a string of four zeros: 0000.CAST(123 AS VARCHAR(10)) converts the number 123 to a string.REPLICATE('0', 4) + CAST(123 AS VARCHAR(10)) results in 0000123.RIGHT(..., 4) takes the rightmost four characters from the result, giving 0123.

Examples

Here are a few more examples for better understanding:

Using FORMAT:

  SELECT FORMAT(5, '0000') AS PaddedNumber  -- Outputs: 0005SELECT FORMAT(45, '0000') AS PaddedNumber -- Outputs: 0045SELECT FORMAT(123, '0000') AS PaddedNumber -- Outputs: 0123SELECT FORMAT(1234, '0000') AS PaddedNumber -- Outputs: 1234

Using RIGHT and REPLICATE:

  SELECT RIGHT(REPLICATE('0', 4) + CAST(5 AS VARCHAR(10)), 4) AS PaddedNumber  -- Outputs: 0005SELECT RIGHT(REPLICATE('0', 4) + CAST(45 AS VARCHAR(10)), 4) AS PaddedNumber -- Outputs: 0045SELECT RIGHT(REPLICATE('0', 4) + CAST(123 AS VARCHAR(10)), 4) AS PaddedNumber -- Outputs: 0123SELECT RIGHT(REPLICATE('0', 4) + CAST(1234 AS VARCHAR(10)), 4) AS PaddedNumber -- Outputs: 1234

8 Months