alpha numeric sequences
Date: Tue, 12 Jun 2012 10:09:50 -0400
Message-ID: <CAE-dsOKFy61rzdhfWH5kU+Wj++3RwpYojZLhanXNpWVsPJtHzg_at_mail.gmail.com>
There are alot of posts on the web about this, but I have not seen what I am looking for.
I have a 10 digit field that is populated with a sequence. We are running out of numbers. Since we interface with external systems, we were told we cannot increase the length of this field. However, we can change it to a varchar and make it alpha-numeric. So we can have A-Z, 0-9 in each digit. (I don't think we want mixed case. That could be confusing). So we can have 36 possible characters in each digit.
I am trying to find the easiest way to do this. I would like to base it on a sequence as a seed. I tried using to_char to convert to hexadecimal, but when I need 11 digits converted, I get a length 11 result back. So this isnt work.
select
to_char(99999999999,'XXXXXXXXX'),length(to_char(9999999999,'XXXXXXXXX'))
from dual
/
TO_CHAR(999 LENGTH(TO_CHAR(9999999999,'XXXXXXXXXX'))
----------- ---------------------------------------- 174876E7FF 11
If I do 10 x's I get back a bunch of pounds.
i saw posts on the web using a with clause. And a modulus and the chr values. The trick with this is that the capital letters in the alphabet are chr 65-90. So when I want digits, i need to use a case statement based on the mod I get back. I also thought about
with (123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
and then using a substring based off a module of a sequence value for each
digit.
Both of these would request that I use a sys_connect by prior and convert
each digit.
There is also the possibility of DBMS_RANDOM and then doing a primary key
query to check for dupes. Not optimal, but it might work.
I wanted to show that I did spend time trying to figure out how to do this. Anyone know the easiest way to do this?
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 12 2012 - 09:09:50 CDT