Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-01
9
Medium Priority
?
569 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 22613160
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
ID: 31501917
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22613600
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:PankajSaluja
ID: 22614246
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
 
LVL 143

Expert Comment

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

Author Comment

by:PankajSaluja
ID: 22618012
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22618066
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
ID: 22618340
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22618378

yes, good catch!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

886 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