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.

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 behind your valuable queries and suggestions in the comment section below. Also, if you think this article helps you, do not forget to share this with your developer community. Happy Coding 🙂