Solved

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

Posted on 2008-10-01
9
545 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 500 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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