0

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.

3
Contributors
3
Replies
5
Views
6 Years
Discussion Span
Last Post by crishlay
1
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))
begin
    select @max = max(id) from #table 
	select @newid = 'A' 

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

insert into #table select @newid

drop table #table

Edited by Tess James: n/a

0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.