Formatting number to add leading zeros - SQL Server

Formatting numbers to add leading zeros can be done in SQL Server. It is just simple. Lets create a new table and see how it works:

CREATE TABLE Numbers(Num INT);

Table Created.

Lets insert few values and see:

    INSERT Numbers VALUES('8');

    INSERT Numbers VALUES('9');

    INSERT Numbers VALUES('10');

    INSERT Numbers VALUES('11');

    INSERT Numbers VALUES('12');

SELECT * FROM Numbers;

1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.

Now we can see how the numbers are formatted with 4 digits, if it has less than 4 digits it will add leading zeros.
Data:

SELECT * FROM Numbers;


Num

8
9
10
11
12

5 row(s) affected.

Formatting:
SELECT RIGHT('0000'+ CONVERT(VARCHAR,Num),4) AS NUM FROM Numbers;

Num

0008

0009

0010

0011

0012

5 row(s) affected.

Posted in: SQL functions,SQL Server