I want to create a sequence that generates values like:
A00000000
A00000001
..
..
..
A99999999
B00000000
B00000001
..
..
It should be generated with the initial alphabetic character A and once it reaches A99999999 it should changed to B and so on.
A normal sequence in Oracle won't give the alphanumeric sequence. How can I generate sequential values in this pattern?
You can create a numeric sequence but convert the first two characters to a hex value on the fly. If you create your sequence as:
... then have a function to simplify, though you don't strictly need it:
The sequence value is always 10 digits. The first two are pulled off and converted to their hex equivalent, and then the rest are appended.
As a demo:
The max value on the sequence means it will go up to F99999999, and then error if you call nextval again. I'm assuming the first digit is supposed to be hexadecimal; if you want A-Z then you can make the sequence start as 6500000000 and convert the first two digits to a character with
chr()
instead as 65 is converted to A, etc.