Formatting of numbers by leading zeros in SQL Server.

In this article, We will discuss How to format of numbers by leading Zeros.

While working on Some project or sometimes the client requirement will come like, i want all values with leading particular number of zeros.

Example:-

I want all the values with 4 leading zeros if no value exists.

if value is 1 then it should show 0001.

if value is 78 then it should show 0078.

if value is 333 then it should show 0333.

but if value if 1234 then it show 1234 because we are working on 4 leading zeros and as it contains value 1234 which is 4 digit so here no preceding zeros will come.

Now lets implement the same thing in SQL Server,

declare @number int = 1;

select format(@number,'0000')

Output=0001

declare @number int = 78;

select format(@number,'0000')

Output=0078

declare @number int = 333;

select format(@number,'0000')

Output=0333

declare @number int = 1234;

select format(@number,'0000')

Output=1234

Note:-  Please make sure that @number should be int.

But what if i have a column which is of type varchar.Can it be possible? OfCouse it is possible. Let me give you an example

Example:-

declare @number int = 86;

select format(convert(int,@number),'0000')