Solved

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

Posted on 2008-10-01
9
538 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
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 142

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
 

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 142

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 142

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 142

Expert Comment

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

yes, good catch!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

912 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

19 Experts available now in Live!

Get 1:1 Help Now