Incrementing Alpha character field sequentially
Posted on 2014-07-25
I have a field in a SQL table called textseq. "AAA" is currently in the field. I need to be able to add "1" to the field and make it "AAB" and then add "1" to it and make it "AAC" until it gets to "AAZ" and then when I add "1" to it I need to increment the second "A" to "B" and then the field would be "ABA" and incrementing one to it will make it "ABB" and so forth.
The reason why I need this is we have a unique serial number that has to be generated I have to be able to increment and then store it because I tie a piece of inventory to it. The number is actually "AAA001" and what they are manually doing is putting the serial number on a speadsheet and adding one to it and when they eventually get to AAA999 they increment the "AAA" to "AAB" and reset the "999" back to "000". It is constantly growing.
I just didn't know if there was a way to do it in SQL or if I had to actually do it in VB. I thought about it being two fields that I would bring back together to make a complete unique serial number. I am just not sure how to increment the text letters by one character.