Some DBMSs have an LPAD() and RPAD() function which can be used to pad numbers with leading and trailing zeros.

SQL Server doesn’t have such a function. But that doesn’t prevent us from being able to pad numbers with leading/trailing zeros.

Non-Integers

First, let’s look at non-integers:

SELECT 
    FORMAT(7, '000.00') AS "1",
    FORMAT(17, '000.00') AS "2",
    FORMAT(73.5, '000.00') AS "3";

Result:

+--------+--------+--------+
| 1      | 2      | 3      |
|--------+--------+--------|
| 007.00 | 017.00 | 073.50 |
+--------+--------+--------+

Here, we use the FORMAT() function to format the number. The first argument is the number, and the second argument is the format string. The function outputs its result as a formatted string.

In the above example, the format string consists of custom numeric format specifiers that results in the original number having zeros added in the places where there’s no digit in the original number. We can use as many zeros in the format string as we need.

SELECT 
    FORMAT(7, '00000.0000') AS "1",
    FORMAT(17, '00000.0000') AS "2",
    FORMAT(73.5, '00000.0000') AS "3";

Result:

+------------+------------+------------+
| 1          | 2          | 3          |
|------------+------------+------------|
| 00007.0000 | 00017.0000 | 00073.5000 |
+------------+------------+------------+

Integers

If the original number is an integer, then we need to do a bit more work:

SELECT 
    REPLACE(FORMAT(7, '00000.0000', 'en-US'), '.', '') AS "1",
    REPLACE(FORMAT(17, '00000.0000', 'en-US'), '.', '') AS "2",
    REPLACE(FORMAT(73.5, '00000.0000', 'en-US'), '.', '') AS "3";

Result:

+-----------+-----------+-----------+
| 1         | 2         | 3         |
|-----------+-----------+-----------|
| 000070000 | 000170000 | 000735000 |
+-----------+-----------+-----------+

Here, I used the REPLACE() function to remove the decimal point after the number has been formatted with it.

I explicitly used en-US as the (optional) third argument to ensure that the decimal separator is a period/full stop, and not another character, like a comma. Some locales use a comma as the decimal separator. If we don’t explicitly specify the locale from within the function, then the locale of the current session is used. Explicitly specifying the locale from within the function ensures that the current session’s locale is not used, and therefore, is unable to interfere with our replace operation.

Azure SQL Edge

Azure SQL Edge is built on a limited implementation of the SQL Server Database Engine, and therefore it supports most of the T-SQL functions that we can use with SQL Server. However, at the time of writing, SQL Edge doesn’t support the T-SQL FORMAT() function.

See How to Add Leading & Trailing Zeros in Azure SQL Edge for an alternative method (which also works in SQL Server).