Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2008-10-01
Medium Priority
565 Views
Last Modified: 2012-08-13
Gernate Alphanumeric series like AA001,AA002....AA999,AB001....... Using C# or SQL2005
0
Question by:PankajSaluja
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 5
• 4
9 Comments

LVL 143

Accepted Solution

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

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

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
``````
0

Author Comment

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
``````
0

LVL 143

Expert Comment

ID: 22614315
yes, sorry for the "mistake", I had created the test table without identity...
0

Author Comment

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

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)

``````
0

Author Comment

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

ID: 22618378

yes, good catch!
0

## Featured Post

Question has a verified solution.

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

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!
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastrâ€¦
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/weâ€¦
###### Suggested Courses
Course of the Month6 days, 9 hours left to enroll

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

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