sequence generation in sql server

Posted by Emil under Sql Server on 11/27/2012 | Points: 10 | Views : 1343 | Status : [Member] | Replies : 3
I had a task of generating a squence in one of the column in a table

sequence format is AAANNN (AAA--Means 3 alphabets and NNN-Means 3 Numbers )

Sequence hould be like
AAA0001 ----- AAA999 ----- AAB0001---AAB999 ----AAC0001----AAC999----it goes



Can anyone have a suggestion for this type of squence?




Responses

Posted by: Krv on: 11/28/2012 [Member] Starter | Points: 25

Up
0
Down
Use the below script to generate alpha sequence
You can use this scirpt block outside your normal int seq generation block and concat

WITH cteINTSequence(SeqNo) 


AS (SELECT 0

UNION ALL

SELECT SeqNo + 1

FROM cteINTSequence

WHERE SeqNo < 1000000),

cteALPHASequence(SeqAlpha)

AS (SELECT CHAR(65 + SeqNo / 17576) + CHAR(65 + (SeqNo%17576) / 676) +

CHAR(65 + (SeqNo%676) / 26) + CHAR(65 + SeqNo%26)

FROM cteINTSequence)

SELECT TOP 100 *

FROM cteALPHASequence

OPTION (MAXRECURSION 0)


Thanks,
R.Vasanth
MCTS(SQL Server & BI)
http://bigdatatech.blogspot.com

Emil, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Emil on: 12/3/2012 [Member] Starter | Points: 25

Up
0
Down
Solution for sequence


--create table #temp(sequence char(6),seq_no int)
--insert into #temp(sequence,seq_no) values ('AAA001',0)
--drop table #temp
--select * from #temp order by 1 desc

declare @ct int = 1
while(@ct <=100)
begin

declare @max_seuence char(6),@character char(3), @number char(3),@max_seq_no int,@final_character char(3)
,@number_character char(3),@final_sequence char(6)
select @max_seuence = MAX(sequence),@max_seq_no=MAX(seq_no) from #temp
select @character = SUBSTRING(@max_seuence,1,3),@number =SUBSTRING(@max_seuence,4,3)
--select @character,@number,@max_seq_no

if(@number=999)
begin
set @max_seq_no=@max_seq_no+1
SELECT @final_character = CHAR(65 + (@max_seq_no%17576) / 676) +
CHAR(65 + (@max_seq_no%676) / 26)
+ CHAR(65 + @max_seq_no%26)

select @number_character = RIGHT('000' + ltrim(STR(1)),3),@number=RIGHT('000' + ltrim(STR(1)),3)
select @final_sequence = @final_character+''+@number_character

end
else
begin

SELECT @final_character = CHAR(65 + (@max_seq_no%17576) / 676) +
CHAR(65 + (@max_seq_no%676) / 26)
+ CHAR(65 + @max_seq_no%26)
select @number_character = RIGHT('000' + ltrim(STR(@number+1)),3),@number=RIGHT('000' + ltrim(STR(@number+1)),3)
select @final_sequence = @final_character+''+@number_character
end

insert into #temp(sequence,seq_no) values (@final_sequence,@max_seq_no)
set @ct=@ct+1
end


Emil, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sourabh07 on: 12/28/2012 [Member] Starter | Points: 25

Up
0
Down
hi..

try this.....


Declare @code varchar(6)

set @code='AAA001'



if cast(RIGHT(@code,3) as int)< 999
begin
if cast(RIGHT(@code,3) as int) < 9
set @code = SUBSTRING(@code,1,3) + '00' +CAST((cast(Right(@code,3) as int)+1) as varchar)
else if cast(RIGHT(@code,3) as int) < 99
set @code = SUBSTRING(@code,1,3) + '0' +CAST((cast(Right(@code,3) as int)+1) as varchar)
else
set @code = SUBSTRING(@code,1,3) + CAST((cast(Right(@code,3) as int)+1) as varchar)
end
else
begin
if SUBSTRING(@code,3,1)<>'Z'
begin
set @code=cast(LEFT(@code,2) as varchar)+
char(cast(ASCII(SUBSTRING(@code,3,1)) as int)+1)+'001'
end
else
begin
if SUBSTRING(@code,2,1)<>'Z'
begin
set @code=cast(LEFT(@code,1) as varchar)+
char(cast(ASCII(SUBSTRING(@code,2,1)) as int)+1)+'A001'
end
else if @code<>'ZZZ999'
begin
set @code=char(cast(ASCII(SUBSTRING(@code,1,1)) as int)+1)+'AA001'
end
end
end

select @code


Sourabh07

Emil, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response