SQL Server Portal

Today, we are going to discuss a most frequent issue with number and that is Leading zero with numbers or we can say that right padding of numbers with zeros in SQL server 2005,2008 & 2012.
In SQL Server 2012, it became very simple with the help of “format” function.

Lets create a sample table to demonstrate it.

Create Table tbl_Sample
(
[SNo] int
,[Values] int
)

Insert into tbl_Sample Values (1,1)
Insert into tbl_Sample Values (2,22)
Insert into tbl_Sample Values (3,333)
Insert into tbl_Sample Values (4,4444)
Insert into tbl_Sample Values (5,55555)
Insert into tbl_Sample Values (6,666666)
Insert into tbl_Sample Values (7,7777777)
Insert into tbl_Sample Values (8,88888888)
Insert into tbl_Sample Values (9,999999999)
GO

Lets browse the table without any formatting.

Select * from tbl_Sample

Now, if you browse the table, you can view the unformated view of column values.

Lets create a function to format it.

FOR SQL SERVER 2005 / 2008

CREATE FUNCTION [dbo].[FN_LPAD_LEADING_ZERO](@Lenght int,@String varchar(Max))
RETURNS Varchar(Max)
AS
Begin
Declare @LPAD_STRING as varchar(Max)
Select @LPAD_STRING =(Case When Len(@String)<=@Lenght THEN
Stuff(SPACE(@Lenght -Len(@String))+@String,1, @Lenght -Len(@String),Replicate(0,@Lenght -Len(@String)))
ELSE @String END)
Return @LPAD_STRING
End
GO

Now, lets browse the table and format it with the Function [FN_LPAD_LEADING_ZERO]

Select [SNO],dbo.[FN_LPAD_LEADING_ZERO] (10,[Values]) as [RPAD_Values] from tbl_Sample

Lets discuss how we will do the same in SQL SERVER 2012, and here “Format” the new function of SQL SERVER 2012 rocks and due to this function, it will be just one line of code.
Let me explain this with simple example.
FOR SQL SERVER 2012

Select [SNO],FORMAT ([Values], '000000000#') as [RPAD_Values] from tbl_Sample