How to get a fixed-length value in SQL Server?

Introduction:

In this article i will explain how to get a fixed length number format in SQL Server (or) how to prefix zero's to number, to get fixed length number format in SQL Server.

Example:

Declare @temp Table(ItemID int identity(99,1), ItemDescription varchar(100))
insert into @temp
select ('AAA')
union all
select ('BBB')
union all
select ('CCC')
union all
select ('DDD')
union all
select ('EEE')

You have a table as below:

select    ID as ItemID,  ItemDescription
from    @temp

Output:

But your requirement is to display ItemID as PID00001 instead of 1 (or) PID00103 instead of 103.

Then change the query as follows:

select    'PID'+RIGHT('00000'+ CONVERT(VARCHAR,(ID)),5) as ItemID,  ItemDescription
from    @temp

Output: