Re: How to generate alpha-numeric sequence in Oracle?

kranilk_at_gmail.com wrote:
> Quesnel & kes,
>
> Thanks for your replies. To answer Quesnel's question, the solution can
> tolerate holes both in the numeric component and the alpha component.
> But, I am not sure about your other question: "Do you care about
> performance for insert/creates ? ". Of course, performance should be
> taken into account. But, I do not think performance is an issue for the
> solution (please see below) I have in mind. Please let me know if that
> is not the case.
>
> I could create a function "alphaSequence" wich contains the following
> code
> offSet := mod(in_val,26);
> ret_val := CHR(ASCII('A') + offSet);
>
> I create a sequence, "alpha_seq" starting from -1 for the alpha part.
> I create another sequence, "numeric_seq" starting with 0 for my numeric
> part.
>
> Then my alpha-numeric sequence can be generated with a function
> containing the following code (please note that it is just a psuedo
> code):
>
> alpha = alphaSequence(alpha_seq.nextval)
> if (alpha = 'A')
> then
> num_val = numeric_seq.nextval
> end if
>
> return alphanum_seq = num_val || alpha (assuming, I format
> the numeric part with proper padding).
>
>
>
>
>
>
> On Nov 1, 1:32 pm, "kes" <abi..._at_gmail.com> wrote:
> > kran..._at_gmail.com wrote:
> > > Hello All,
> >
> > > After searching the web unsuccessfully for what I am looking for, I
> > > have come here.
> > > I need to generate a 6 digit alpha-numeric sequence of values that
> > > looks like the following.
> > > 00001A
> > > 00001B
> > > :
> > > 00001Z
> > > 00002A
> > > 00002B
> > > :What about creating a regular sequence, then using this:http://www.oracle.com/technology/sample_code/products/rdb/files/conve...
> >
> > to create your requisite values?
> >
> > Alexhttp://www.lifesabirch.org/

Keep in mind that an Oracle sequence can have gaps, so if the alpha_seq is a looping sequence, isn't it possible to miss the 'A'?

I would suggest one sequence and a function that reformats it. What the customer wants is just the lower digits of a number represented in base 26 (A-Z) and the rest of the number in decimal. So infact you might store the value as a number and convert it when you need to display it.

X:=coded_seq.nextval;

letter part := CHR(ASCII('A') + mod(X,26); digit part := trunc(X,0);
then format the digit part appropriately.

Advantages:
No special cases, miscoded IFs. The code is straight line calculations.

And you can use a numeric attribute to the table instead of character, so you cannot store incorrectly formatted values. (convert to/from the character format only in the UI and reports) If you use CHAR or VARCHAR to store your key, then you need to insert triggers to reject bad data like '0JUNKIT'

And BTW, try not to top post.
  HTH,
  ed

1. Because it fouls the order in which people normally read text.
2. Why is top-posting such a bad thing?
3. Top-posting.
4. What is the most annoying thing on usenet and in e-mail?