I want to print a sequence of letters and concatenate it with an existing string-CodePudding

I have a requirement where i need to add an alphabet A to Z sequentially to a string. example : OGCP223000 A OGCP223000 B,etc

So that I get

OGCP223000A,OGCP223000B till OGCP223000Z, and start again every time I run the SP. So one time I call the stored procedure I want A, and the next time I want B, and the time after that i want C, and so on Please help

I used numbers by creating a sequence in SQL server, but could not for Alphabets

CodePudding user response：

You can use a sequence to produce a cycle of alphabetic values. Start with a sequence that cycles through values from 1 to 26:

create sequence AlphaValue as Int MinValue 1 MaxValue 26 Cycle;

Then you can use Char to convert the returned value into the corresponding letter:

declare @Count as Int = 0;
declare @AlphaValue as Int;

while @Count < 30
  begin
  set @AlphaValue = next value for AlphaValue;
  select @Count as Count, @AlphaValue as AlphaValueInt,
    -- Convert the 1..26 value into the corresponding letter.
    Char( ASCII( 'A' )   @AlphaValue - 1 ) as AlphaValueChar;
  set @Count  = 1;
  end;

dbfiddle.

Note that the usual warnings about sequences sometimes skipping values apply.

CodePudding user response：

Thank you, was able to figure it out, with your help of course.. Please see the code below, This will print A to Z individually every time I run this block of code

Declare @Alphabet TABLE (Alpha1 VARCHAR(1), Alphaint VARCHAR(2), Count1 INT)

Declare @Count as Int = 0;

Declare @AlphaValue as Int;

while @Count < 30

begin

set @AlphaValue = next value for AlphaValue;

INSERT @Alphabet (Count1, Alphaint, Alpha1)

select @Count as Count, @AlphaValue as AlphaValueInt, -- Convert the 1..26 value into the corresponding letter. Char( ASCII( 'A' ) @AlphaValue - 1 ) as AlphaValueChar; set @Count = 1;

end;

Select Alpha1 from @Alphabet where Count1=0