increment string 1

[dky1e]

Hi, We have a list of serial numbers stored in the database and I need to get the next possible serial number by incrementing the highest serial number string in the database.

The serial number is of type varChar. How can I increment its value, that is if its 'aa' the next one would be 'ab' or if it's 'a1' I want to increment it to 'a2'.

Any suggestions?

Thank you!

 

[tlbroadbent]

Here is a simple example using the CHAR and ASCII functions.

Select left('ab',1) + char(Ascii(right('ab',1))+1)
Select left('a1',1) + char(Ascii(right('a1',1))+1)

Of course you'll have to enhance this query but it should get you started. 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.

 

[dky1e]

That was my first idea, but I decided to stop thinking there is a better solution. The serial number consist of 10 characters and for example if I increment "aaz" I want to get "ab0", or something to that effect. I need to get it to overlap properly until it reaches 'zzz...'.

How does the autoincrement function work? Does it only work with integers?

It seems like a common problem, but I can't find anything on BOL.

 

[sunila7]

Hi,

Expanding on what Terry has said

Select Case char(ascii(right('za' ,1)))
When '9' then
char(ascii(left('za',1))) + 'a'
when 'z' then
char(ascii(left('za',1))+1) + '1'
else
char(ascii(left('za',1))) + char(ascii(right('za',1))+1)
end

this SQL should go till zz.... i am assuming that the code is of length 2

Sunil

 

[tlbroadbent]

I assumed you'd need to modify the script. As Sunil has shown it is possible. The Identity column will autoincrement but it can only be a numeric data type.

In SQL 2000 you could create a user-defined function to increment your column. It would have code similar to Sunil's. 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.

 

[dky1e]

I don't like this. Does sql have arrays?

 

[dky1e]

Or even better, can I call an executable from sql?

This is what I want to do (pseudo code):
inputstring // user serial number input
nextSN //next serial num
charArray = { 0, 1, [...], a, b, c, [...], z}
for each char in char array starting with first char in inputString
begin
[...]
for each char in char array starting with last char in inputString
begin
if( get next char if possible )
begin
print the sn
done!
end
end
end

this will do the trick , if number is abb it will make it abc and if it's abz it will make it ac0.

 

[tlbroadbent]

SQL has no array data type. You can simulate an array by using substring functions. Here is another example, using substrings and WHILE loops. Modify as need for your application. I coded this for fixed length strings with leading spaces. I also haven't accounted for the string containing all z's.

create table #tmp (StrData char(10), recid int identity)
set nocount on
insert #tmp values('aaaaaabzzz')
insert #tmp values(' zzzz')
insert #tmp values(' abz0z')
insert #tmp values('aaaaaaazzy')
insert #tmp values('wzzzzzzzzw')
insert #tmp values('wzzzzzzzzz')
insert #tmp values('aaaaaabz9z')

go
declare
@str char(10),
@cn tinyint, @cs char(1), @done tinyint,
@rn tinyint, @mn tinyint
Select @rn=1, @mn=max(recid) From #tmp
While @rn<=@mn
Begin
Select
@cn=10, @done=0,
@str=StrData From #tmp Where RecID=@rn
Print @str
While @done=0 And @cn>0
Begin
Select @cs=substring(@str,@cn,1)
If (@cs>='a' And @cs<='y') Or (@cs>='0' And @cs<='8')
Begin
Select @cs=char(ascii(@cs)+1)
Select @str=Stuff(@str,@cn,1,@cs), @done=1
End
Else
If @cs='9'
Begin
Select @cs='a'
Select @str=Stuff(@str,@cn,1,@cs), @done=1
End
Else
If @cs='z'
Begin
Select @cs='0'
Select @str=Stuff(@str,@cn,1,@cs), @cn=@cn-1
End
Else
If @cs=' '
Begin
Select @cs='a'
Select @str=Stuff(@str,@cn,1,@cs), @done=1
End
End
Select @rn=@rn+1
Print @str
Print ''
End
go

Drop table #tmp Terry L. Broadbent - DBA
Computing Links:

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

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[dky1e]

WOW! Thanks!