Solved

Gernate Alphanumeric series like AA001,AA002....AA999,AB001....... Using C# or SQL2005

Posted on 2008-10-01
9
537 Views
Last Modified: 2012-08-13
Gernate Alphanumeric series like AA001,AA002....AA999,AB001....... Using C# or SQL2005
0
Comment
Question by:PankajSaluja
  • 5
  • 4
9 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
do you want that as primary key automatically generated?
well, don't.  you might generate a plain sequence (identity), and then present that value into the AA001 display.
0
 

Author Closing Comment

by:PankajSaluja
Comment Utility
Yea, u got the question
i want present value this sequence like AA001 , AA002...AA999 , AB001.....
So give the complete solution if possible in SQL 2005 else C# , ASP.net2.0
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
check this table out:

declare @t table 

( id int identity

, id_display as char(65 +  (id / (26*1000) ) ) + char(65  + ( (id / 1000)%26)   ) + right( '00' + cast( (id % 1000 )  as varchar(3)),3)

)
 
 
 

and insert some rows, and see the id_display
 
 
 

declare @x int

set @x = 1

while @x < 2000

begin

  insert into @t ( id ) values (@x)

  set @x = @x + 1

end
 

select * from @t

Open in new window

0
 

Author Comment

by:PankajSaluja
Comment Utility
Solution is ok
But there is slight mistake

  insert into @t ( id ) values (@x)
  set @x = @x + 1

Id is identity column so we can not insert manually, so we to take some other column to enter the values

and i have create a function which return a single varchar value which would help in gernating unique alpha numeric id

here is function


CREATE function [dbo].[GetIDSeries] (@ID int)    

returns varchar(10)    

as    

begin    

declare @Ret_Value varchar(10)  

  

select @Ret_Value =  char(65 +  (@ID / (26*1000) ) ) + char(65  + ( (@ID / 1000)%26)   )   

+ right( '00' + cast( (@ID % 1000 )  as varchar(3)),3)  

  

return @Ret_Value  

  

end    

Open in new window

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
yes, sorry for the "mistake", I had created the test table without identity...
0
 

Author Comment

by:PankajSaluja
Comment Utility
that 's ok, no problem , happens  Appraisable for quick response

one thing more
suppose we need series like AA000001.....AA999999,AB000001 then i should go....
Means  AA<6digits>  then AB000001
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
you will have only to change the 1000 into 1000000 and the (3) into (6), and use BIGINT instead of INT...


select @Ret_Value =  char(65 +  (@ID / (26*1000000) ) ) + char(65  + ( (@ID / 1000000)%26)   )   

+ right( '00' + cast( (@ID % 1000000 )  as varchar(6)),6) 
 

 

Open in new window

0
 

Author Comment

by:PankajSaluja
Comment Utility
yea this is ok
but have change this line
+ right( '00' + cast( (@ID % 1000000 )  as varchar(6)),6)
as
+ right( '00000' + cast( (@ID % 1000000 )  as varchar(6)),6)
to get
AA000001.....AA999999,AB000001


0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility

yes, good catch!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How can I get this column in my query? 2 28
crm development 2 34
SQL Restore Script - Syntax Error 8 69
Hide Tab Page 3 18
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now