RE: alpha numeric sequences
- From: "Lange, Kevin G" <kevin.lange@xxxxxxxxxx>
- To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 12 Jun 2012 10:02:22 -0500
I have a warning for you on this.
Unless you want certain words showing up in your alpha numeric sequence,
I would take all the vowels out of your base 36 string and use base 31
instead. I did not do this when I first put alpha-numeric sequences on
our database and was slightly embarased when a client pointed it out to
us.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Dba DBA
Sent: Tuesday, June 12, 2012 9: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
This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
