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

 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/
Received on Wed Nov 01 2006 - 17:25:22 CST