SQL Server: How to get fixed length number format in SQL Server (or) How to prefix zeros to number, to get fixed length number format in SQL Server

 SQL Server     sbo     Updated on Jul 24, 2013     VIKRAM     1574   

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:

 

  Related Articles
SQL Server: Variables
SQL Server: Differences between CHAR, NCHAR, VARCHAR and NVARCHAR
SQL Server: System Databases
SQL Server: Insert values into Identity Column
ASP.Net: List View Basic Example
JQuery: How to replace string in JQuery
Java Script: How to replace string in Java Script
C#: How to replace string in C#
SQL Server: How to replace string in SQL Server
C#: How to convert string to int in C#
  Recent Articles
SQL Server: Variables
SQL Server: Differences between CHAR, NCHAR, VARCHAR and NVARCHAR
SQL Server: System Databases
SQL Server: Insert values into Identity Column
ASP.Net: List View Basic Example
JQuery: How to replace string in JQuery
Java Script: How to replace string in Java Script
C#: How to replace string in C#
SQL Server: How to replace string in SQL Server
C#: How to convert string to int in C#