The simple,efficient solution to avoiding duplicates with your existing all-numeric identifiers is to start with A000000000 and go from there. All of your all-numeric values will be below that. (or, following another contributor's suggestion, start with B000000000 if you plan to omit vowels) Mike -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Dba DBA Sent: Tuesday, June 12, 2012 10:55 AM To: ORACLE-L Subject: Re: alpha numeric sequences I think I found what I am looking for on the web. I basically want to convert from a base 10 to a base 36 number system. This query below goes from base 36 to base 10. So I need to reverse it. Once I figure out how it works... Since I already made this thread. Does anyone think there is an easier way to do this? I was hoping to do this? This is not that hard. Since we already have 9 digits of number values already, I can't allow anything that comes back with all digits so I don't get duplicates. So I played to add a to_number() around the query. if I get an exception, that means I have at least 1 character, so I return, if not, I have all digits and I call the function again recursively. http://stackoverflow.com/questions/2568668/base-36-to-base-10-conversion -using-sql-only On Tue, Jun 12, 2012 at 10:09 AM, Dba DBA <oracledbaquestions@xxxxxxxxx>wrote: > 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? > -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l