I would like to auto-generate a column which has a mixture of both numbers and alphabets. Like AA01 or AC24. I want to start at AA00 until AA40. From here i will go to AB00 until AB40, and do likewise for other subsequent alphabets until the last one which will be AZ40. I just need a start off. I understand i can make use of the ASCII key but dont really understand how. Any help or suggestion is appreciated. Thank you.

Recommended Answers

All 3 Replies

create table #table 
   id varchar(10)

select * from #table

declare @max varchar(10)
declare @default varchar(10)
declare @newid varchar(10)
declare @alphabet char(1)
declare @number char(2)

select @default = 'AA00'

if(exists (select 1 from #table))
    select @max = max(id) from #table 
	select @newid = 'A' 

	if( right(@max,2) <> '40')
		select @number = convert(varchar,right(@max,2) + 1)
		select @alphabet = substring(@max,2,1)
		select @number = '0'
		select @alphabet = char(ascii(substring(@max,2,1)) + 1)
    select @newid = @newid + @alphabet + case when @number < 10 then  '0' + @number else @number end
	select @newid = @default
select @newid

insert into #table select @newid

drop table #table

Thank you very much for your help. Now i want to put it in a stored procedure because i will select the stored procedure as my data-source in C#. Let me work on this first to avoid troubling you again.

good example for Auto generate alpha-numeric column using MS-SQL query

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.