Formatting number to add leading zeros

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('12');
    INSERT Numbers VALUES('112');
    INSERT Numbers VALUES('12');
    INSERT Numbers VALUES('122');
    INSERT Numbers VALUES('122');

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 6 digits, if it has less than 6 digits it will add leading zeros.
Data:

SELECT * FROM Numbers;

Num
12
112
12
122
122

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

NUM
000012
000112
000012
000122
000122

(OR)

SELECT RIGHT(REPLICATE('0', 10) + CONVERT(VARCHAR, Num), 6) AS NUM FROM Numbers;

NUM
000012
000112
000012
000122
000122

Mr. Ravi Krishna - Senior Software Engineer

I am a Mocrosoft ASP.net Developer and MCP Certified professional. I have overall 5 years of experience in IT Industry,in that 3 years experience in Microsoft BI(SSAS,SSIS,SSRS). I have experience on various business domains like Automation and Chemical.

https://sites.google.com/site/rkkumardotnet/