How to Format Numbers with Leading Zeros in SQL Server?

In this SQL Server tutorial, you will learn about format numbers with leading zeros in SQL Server.

First, you will be introduced to the concept of leading zero, and then how it is beneficial in several ways.

After that, you will learn how to use the FORMAT() function to format the number with leading zeros. Finally, you will use the RIGHT(), CONVERT(), and REPLICATE() functions in combination to format the given number or column value with leading zeros.

Format Number with Leading Zeros in SQL Server

Before moving to format number with leading zeros in SQL Server, you need to know ‘What leading zero means?’. The leading zero means adding the zeros before a numeric value or adding zeros to the left of the number.

For example, if you have the number 23 and you add two zeros before the number like 0023, this means you have formatted the number 23 with two leading zeros to make sure that the total length of the number should be 4 characters long.

There are several reasons or benefits of formatting the number with leading zeros.

• Sometimes some column in a table contains values that should be displayed in a consistence way when you present that data or value. For example, invoice numbers, employee IDs, reference numbers, codes, etc.
  • When you format these numbers with leading zeros, it becomes easier to read the numbers.

• For a visual look, presenting the number or data with leading zeros improves the overall appearance.
• Formatting numbers with leading zeros is very helpful in sorting and ordering.

These are some benefits by which you get the idea of why you need to format the number with leading zeros. Let’s move ahead and learn how to format number with leading zeros in SQL Server.

In SQL Server, there is a function called FORMAT() function, using this function you can format the given number with leading zeros.

The syntax is given below.

FORMAT(value_exp, format)

Where,

• FORMAT(): It is the function that formats the given below.
• value_exp: It is the value, expression, and can column that want to format with leading zeros.
• format: It is the pattern that decides how to format the number.

Let’s take a simple example by executing the below query.

SELECT FORMAT(102,'0000') AS FormattedWithZero;

Look at the above query output, which formatted the given number 102 with one leading zero. After formatting the number it became 0102.

But here you may think about the format (‘0000’) that is specified within the FORMAT() function. This format pattern (‘0000’) means after formatting the number with zeros it should only contain four characters or the width of the number must be four digits long.

So when the FORMAT() function formats the number 102 which is already three characters long, the function adds only one zero before that number to maintain the specified width which is 4 in this case.

So the number of zeros you provide as a format pattern to the FORMAT() function, formats the given number to that length of the number of zeros in the format pattern.

For example, look at the below query and its output.

SELECT FORMAT(6, '00000') ----> Output: 00006

SELECT FORMAT(46,'00000') ----> Output: 00046

SELECT FORMAT(946, '00000') ----> Output: 00946

SELECT FORMAT(7946,'00000') ----> Output: 07946

Look at the above example, and think about how leading zeros are added to each number to maintain the specified width.

Now I hope that you have a clear understanding of how to use the FORMAT() function to format numbers with leading zeros.

Let’s see how to use the FORMAT() function on the table and format the column value with leading zeros. The table is shown below.

In the above picture, the OrderID column contains the order IDs of each order made by the customer. As you can see the length of the order ID is not fixed such as 1, 10 etc.

Now you want to fix the length or width of the order ID with leading zeros. This means you want the order ID should be 6 digits long, so instead of 1 and 10, it should be 00001 and 000010.

So use the below query to format the number or order ID within the OrderID column with leading zeros.

SELECT OrderID, FORMAT(OrderID,'000000') As FormattedLeadingZeros 
FROM CustomerOrders;

After the execution of the above query, the order ID of the OrderID column in the CustomerOrders table is formatted with leading zeros.

For example, 11 is the order ID which is formatted to 000011 and if you count the number of digits in this formatted number, you get a total of 6 digits or you will find the the length of each order ID equal to 6 digits long.

Format Number with Leading Zeros in SQL Server Using Right and Convert Function

You can also use the RIGHT() and CONVERT() functions in combination to format the number with leading zeros. But here, first, you will convert the number to a string using the CONVERT() function, because the RIGHT() function works on the string value.

The RIGHT() function extracts the rightmost character from the given string value based on the specified width. The CONVERT() function converts the given value data type to another data type.

So let’s take the same example that you have done in the above sections. So use the below query.

SELECT RIGHT('000000' + CONVERT(VARCHAR,OrderID),6) AS FormattedNumber
FROM CustomerOrders;

Again you can see the number within the OrderID column is formatted with leading zeros and the length of the number is 6 digits long such as 000012 order ID.

Look at the query part RIGHT(‘000000’ + CONVERT(VARCHAR, OrderID),6), Here as you know RIGHT() function extracts the rightmost characters from the string.

For example, take any order ID ( such as 1) and do this ‘000000’ + ‘1’ then the value becomes 0000001, the RIGHT(‘0000001’, 6) is how each order ID is represented within the RIGHT() function.

Then the RIGHT() function extracts the rightmost 6 characters from the ‘0000001’, after extraction, the string becomes from ‘0000001’ to ‘000001′. The same process is applied for all the order ID numbers within the OrderID column.

Here the ‘000000’ + CONVERT(VARCHAR, OrderID) function converts each order ID value into varchar (string), and then 6 zero in the form of string is added to each converted string.

This is how to format number with leading zeros in SQL Server using the RIGHT() and CONVERT() function together.

Format Number with Leading Zeros in SQL Server Using Right, Replicate and Convert Function

In the previous section, you used the RIGHT() and CONVERT() functions to format the number with leading zeros. Here you will one more function REPLICATE() with those two functions to format numbers with leading zeros.

The REPLICATE() function in SQL Server replicates or makes a copy of the given strings based on the specified number.

For example, REPLICATE(‘T’,5) means creating a copy of the string ‘T’ five times, so the output will be ‘TTTTT’. Here the specified number is 5 and the string is ‘T’.

Let’s use the REPLICATE() function with RIGHT() and CONVERT() to format the number with leading zeros.

SELECT RIGHT(REPLICATE('0',6) + CONVERT(VARCHAR,OrderID),6) AS FormattedNumber
FROM CustomerOrders;

The output shows each order ID with leading zeros and 6 characters in length. For example, 000007 is a formatted order ID with leading zeros.

But if you compare the query part RIGHT(REPLICATE(‘0’,6) + CONVERT(VARCHAR, OrderID),6) with the query part of the previous section RIGHT(‘000000’ + CONVERT(VARCHAR, OrderID),6) where you have used only RIGHT() and CONVERT() function.

If you see in the above query the REPLICATE(‘0’,6) function works the same as the 6 zeros (‘000000’) in the previous section of the query part.

Well, now you have a proper understanding of how to use the FORMAT() function or RIGHT(), CONVERT(), and REPLICATE() to format number with leading zeros in SQL Server.

Conclusion

In this SQL Server tutorial, you learned about formatting the number with leading zeros using the FORMAT() function in sql server. After that, format a number within the column using the RIGHT(), CONVERT(), and REPLICATE() functions.

You may like:

• Format Number with Commas in SQL Server
• How to Format Number with Commas and Decimal in SQL Server?
• Convert Int to Fixed Length String in SQL Server

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.