Increment ALPHA characters 1

[gbraden]

I been messing with this all morning. The requirement is that a record prefix (say NN) must get incremented with each new record (NO, NP, NQ...ZZ). Once ZZ is reached, it would start again at AA.

I tried adding 1 to the ASCII conversion for the last character but the set does not take, and NN remains NN. Is there a simple way to do this? Seems like I should not be the first one that had this as a requirement.

I am currently using SQL Server 2000.

Thanks in advance!


Glen Braden

 

[gmmastros]

This ??

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]VarChar[/color](2)
[COLOR=blue]Set[/color] @Temp = [COLOR=red]'FZ'[/color]

[COLOR=blue]Select[/color] [COLOR=blue]Case[/color] [COLOR=blue]When[/color] @Temp = [COLOR=red]'ZZ'[/color] 
            [COLOR=blue]Then[/color] [COLOR=red]'AA'[/color]
            [COLOR=blue]Else[/color] [COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=#FF00FF]Right[/color](@Temp, 1) = [COLOR=red]'Z'[/color] 
                      [COLOR=blue]Then[/color] [COLOR=blue]Char[/color]([COLOR=#FF00FF]ASCII[/color]([COLOR=#FF00FF]Left[/color](@Temp, 1)) + 1) + [COLOR=red]'A'[/color]
                      [COLOR=blue]Else[/color] [COLOR=#FF00FF]left[/color](@Temp, 1) + [COLOR=blue]Char[/color]([COLOR=#FF00FF]ASCII[/color]([COLOR=#FF00FF]Right[/color](@Temp, 1)) + 1)
                      [COLOR=blue]End[/color]
            [COLOR=blue]End[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nice95gle]

This should work. You might have to alter it a little to fit your needs

DECLARE @MaxR varchar(2)
SET @MaxR = (Select char(ascii(Left(Right('ZZ',5),1))+1))

IF @MaxR='[' 
BEGIN 
do something
END

Well Done is better than well said
- Ben Franklin

 

[nice95gle]

Or you can use George's way. My way was for a project I was doing so it was not tested...

Well Done is better than well said
- Ben Franklin

 

[gmmastros]

Actually, the best way would be to create another table.

[tt]
Code NextCode
-------- ------
AA AB
AB AC
AC AD
[green]etc...[/green]
AZ BA
BA BB
[green]etc...[/green]
ZZ AA
[/tt]

Then, a simple join to this table will return the next value.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gbraden]

Okay... Thanks for all the help! George, you were right on. I have the following code running (and working).

Declare @Temp VarChar(2)
Set @Temp = (Select msnIdentification from logSequenceCounter
		where SequenceName='ATOID') 	

set @temp =
(Select Case When @Temp = 'ZZ'
            Then 'AA'
            Else Case When Right(@Temp, 1) = 'Z'
                      Then Char(ASCII(Left(@Temp, 1)) + 1) + 'A'
                      Else Left(@Temp, 1) + Char(ASCII(Right(@Temp, 1)) + 1)
                      End
            End)

select @temp
Update logSequenceCounter SET msnIdentification = @Temp
where SequenceName='ATOID' and SequenceYear = 1111
go

Glen Braden