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

From: Ed Prochak <edprochak_at_gmail.com>
Date: 5 Dec 2006 09:50:09 -0800
Message-ID: <1165341006.966064.12950@j72g2000cwa.googlegroups.com>

On Dec 1, 3:45 pm, "Ed Prochak" <edproc..._at_gmail.com> wrote: []
>
> 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);

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

That should be

   digit part := trunc(X/26,0);
(I hate making typos! My apologies!)

> 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'
>
Received on Tue Dec 05 2006 - 11:50:09 CST