sequence generation in sql server

Posted by Emil under Sql Server on 11/27/2012 | Points: 10 | Views : 2401 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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?

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

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

0	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

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

Latest Posts