Azure SQL Edge uses a limited version of the SQL Server Database Engine. One of the limitations of Azure SQL Edge, is that it doesn’t currently support CLR-dependent T-SQL functions, such as the FORMAT() function.

This can be a problem when trying to format numbers.

However, there are often ways around such limitations. Here are examples of how we can pad numbers with leading and trailing zeros in SQL Edge.

Non-Integers

We can concatenate a string representation of the number with a bunch of zeros, then use the RIGHT() or LEFT() functions to return the right or left part of the concatenated string. The function we use will depend on whether we want to return leading zeros or trailing zeros.

Leading Zeros

Here’s an example of padding a number with a leading zero:

SELECT RIGHT('00000' + CAST(7.35 AS varchar(4)), 5);

Result:

07.35

Here’s how it looks with a few more numbers:

SELECT 
    RIGHT('0000000' + CAST(7.35 AS varchar(7)), 7) AS "1",
    RIGHT('0000000' + CAST(0.35 AS varchar(7)), 7) AS "2",
    RIGHT('0000000' + CAST(735.00 AS varchar(7)), 7) AS "3",
    RIGHT('0000000' + CAST(735.99 AS varchar(7)), 7) AS "4";

Result:

+---------+---------+---------+---------+
| 1       | 2       | 3       | 4       |
|---------+---------+---------+---------|
| 0007.35 | 0000.35 | 0735.00 | 0735.99 |
+---------+---------+---------+---------+

Trailing Zeros

Here’s an example of padding a number with a trailing zero:

SELECT LEFT(CAST(7.35 AS varchar(4)) + '00000', 5);

Result:

7.350

And with a few other numbers:

SELECT 
    LEFT(CAST(7.35 AS varchar(7)) + '0000000', 7) AS "1",
    LEFT(CAST(0.35 AS varchar(7)) + '0000000', 7) AS "2",
    LEFT(CAST(735.00 AS varchar(7)) + '0000000', 7) AS "3",
    LEFT(CAST(735.99 AS varchar(7)) + '0000000', 7) AS "4";

Result:

+---------+---------+---------+---------+
| 1       | 2       | 3       | 4       |
|---------+---------+---------+---------|
| 7.35000 | 0.35000 | 735.000 | 735.990 |
+---------+---------+---------+---------+

Integers

When working with integers, we can multiply the integer by an amount that gives us the desired amount of trailing zeros. We can also cast it as an integer, just in case we get a non-integer.

Here’s an example of padding a number with leading and trailing zeros:

SELECT RIGHT('0000000000' + CAST(CAST(7 * 10000 AS int) AS varchar(10)), 10);

Result:

0000070000

Here it is with some other values (including non-integers):

SELECT 
    RIGHT('0000000000' + CAST(CAST(1234 * 10000 AS int) AS varchar(10)), 10) AS "1",
    RIGHT('0000000000' + CAST(CAST(0.123 * 10000 AS int) AS varchar(10)), 10) AS "2",
    RIGHT('0000000000' + CAST(CAST(123456 * 10000 AS int) AS varchar(10)), 10) AS "3",
    RIGHT('0000000000' + CAST(CAST(1234.567 * 10000 AS int) AS varchar(10)), 10) AS "4";

Result:

+------------+------------+------------+------------+
| 1          | 2          | 3          | 4          |
|------------+------------+------------+------------|
| 0012340000 | 0000001230 | 1234560000 | 0012345670 |
+------------+------------+------------+------------+