Menu Close

Formatting number to add leading zeros in SQL Server


In this article we learn about Formatting number to add leading zeros in SQL Server. Please read the previous article Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery.

For more details about SQL Server please visit this link.

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);

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

Conclusion

So far in this article we learnt about Formatting number to add leading zeros in SQL Server.


Leave a Reply

Your email address will not be published.