First, the character "A" can be represented as CHR(65), "B" as CHR(66),and so on.
The answer lies in using two Oracle functions: MOD and TRUNC.
-- MOD gives the remainder of two numbers. For example, MOD(50,26) gives 24, which is the remainder of 50/26.
-- TRUNC gives just the integer value of a number. So TRUNC(50/26) gives 1, which is the integer value of 1.92307692...
So, you want a sequence of characters AAA, AAB, AAC, ...
The last character is simply chr(MOD(x,26)+64)
The second-to-last character is chr(MOD(TRUNC(x/26),26)+65)
The third-to-last character is chr(MOD(TRUNC(x/26)/26),26)+65)
Putting it all together:
SELECT chr(MOD(TRUNC(x/26)/26),26)+65)|| chr(MOD(TRUNC(x/26),26)+65)||
chr(MOD(x,26)+64)
You can replace "x" above with SEQUENCE_NAME.NEXTVAL. This is good for a three- character sequence.
Subscribe to:
Post Comments (Atom)
Firstly, this is great. Exactly what I needed to create a unique 6 character ID where 1m records weren't enough....one small point though, using the +64 causes a problem at the 26th increment, by showing @ instead of A - chr(MOD(27,26)+64) = '@' - to prevent this, I just put in +65 - i.e.
ReplyDeleteSELECT chr(MOD(TRUNC(x/26)/26),26)+65)|| chr(MOD(TRUNC(x/26),26)+65)||
chr(MOD(x,26)+65)
This means you'll start with AAB instead of AAA...you could put in a case statement but it's not worth it for me.
Thanks again.