Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

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

 

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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

581 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