Adding a number to a string

[DwayneL]

Hello again,

does anybody know how to add a number to a string

like

"AAAA" + 1

 

[Andel]

select 'AAAA' + convert(varchar(5), 1)

Andel
andel@barroga.net

http://andyb.8m.com

 

[DwayneL]

sorry, its my fault for not being more specfic. I need AAAA to be AAAB.

 

[Glitchbane]

What happens when you get to "AAAZ" and want to add 1? Do you go to "AAAa" or "AABA" or "AAA0" or what? You need to be more specific still.

 

[DwayneL]

ok then

i need to do something like this

AAAA + 1 = AAAB
AAAB + 1 = AAAC
AAAZ + 1 = AABA
AABA + 1 = AABB
AAZZ + 1 = ABAA
BGDZ + 1 = BGEA
FZZZ + 1 = GAAA

 

[tlbroadbent]

This is not elegant but it works. Hopefully, someone can create a better solution.

Declare
@val char(4),
@c1 char(1), @c2 char(1),
@c3 Char(1), @c4 char(10)

Set @val='fZZZ'
Set @val=Upper(@val)

Set @c1=substring(@val,1,1)
Set @c2=substring(@val,2,1)
Set @c3=substring(@val,3,1)
Set @c4=substring(@val,4,1)

If @c4='Z'
Begin
Set @c4='A'
If @c3='Z'
Begin
Set @c3='A'
If @c2='Z'
Begin
Set @c2='A'
If @c1='Z'
Begin
Set @val=Null
End
Else
Set @c1=char(ascii(@c1)+1)
End
Else
Set @c2=char(ascii(@c2)+1)
End
Else
Set @c3=char(ascii(@c3)+1)
End
Else
Begin
Set @c4=char(ascii(@c4)+1)
End

If @val Is Null
Print 'Invalid value'
Else
Begin
Set @val=@c1+@c2+@c3+@c4
Print @val
End Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[plantj]

This is ugly but it works and my be of interest...

declare @cABValue char(4)
declare @iABValue int
declare @iAsciiOffset int
declare @s varchar(80)

/* Initial value */
select @cABValue='ABCD'


/* Get the offset value for integer conversion */
select @iAsciiOffset=ascii('A')-1

/* Convert ABValue to an integer */
select @iABValue
=(ascii(substring(@cABValue,1,1))-@iAsciiOffset)*power(26,3)
+(ascii(substring(@cABValue,2,1))-@iAsciiOffset)*power(26,2)
+(ascii(substring(@cABValue,3,1))-@iAsciiOffset)*26
+(ascii(substring(@cABValue,4,1))-@iAsciiOffset)

select @s='@iABValue='+convert(varchar,@iABValue)
print @s

/* Add 1 */
select @iABValue=@iABValue+1

/* Convert back to ABValue */
select @cABValue
=char(@iAsciiOffset+@iABValue/power(26,3))
+char(@iAsciiOffset+(@iABValue-((@iABValue/power(26,3))*power(26,3)))/power(26,2))
+char(@iAsciiOffset+(@iABValue
-((@iABValue/power(26,3))*power(26,3))
-(((@iABValue-((@iABValue/power(26,3))*power(26,3)))/power(26,2))*power(26,2)))/26)
+char(@iAsciiOffset+@iABValue
-(@iABValue/power(26,3))*power(26,3)
-((@iABValue-((@iABValue/power(26,3))*power(26,3)))/power(26,2))*power(26,2)
-((@iABValue
-((@iABValue/power(26,3))*power(26,3))
-(((@iABValue-((@iABValue/power(26,3))*power(26,3)))/power(26,2))*power(26,2))
)/26)*26)

print @cABValue

/* Add 26 - should give ABDD */
select @cABValue='ABCD'
select @iABValue=@iABValue+26

/* Convert back to ABValue */
select @cABValue
=char(@iAsciiOffset+@iABValue/power(26,3))
+char(@iAsciiOffset+(@iABValue-((@iABValue/power(26,3))*power(26,3)))/power(26,2))
+char(@iAsciiOffset+(@iABValue
-((@iABValue/power(26,3))*power(26,3))
-(((@iABValue-((@iABValue/power(26,3))*power(26,3)))/power(26,2))*power(26,2)))/26)
+char(@iAsciiOffset+@iABValue
-(@iABValue/power(26,3))*power(26,3)
-((@iABValue-((@iABValue/power(26,3))*power(26,3)))/power(26,2))*power(26,2)
-((@iABValue
-((@iABValue/power(26,3))*power(26,3))
-(((@iABValue-((@iABValue/power(26,3))*power(26,3)))/power(26,2))*power(26,2))
)/26)*26)

print @cABValue

 

[DwayneL]

thank you, Its not about style points, it about getting it working!