To: Ryan McAleer 
> On Thu, 5 Aug 1999, Ryan McAleer wrote:
> 
>  > Dear Ari,
>  > I wonder if I might pick your brain a little. I need to create a sequence
>  > that follows the string 'bogus' in a field. Can this be done in sql or
>  > sql*plus. If so, can you send me a sample. I need to do this so that I 
> can
>  > create a primary key that uses that particular field.
>  > Thanks for any input,
>  >
>  > Ryan McAleer
>  > ryanmmm@hotmail.com

Ryan,

It took a while, but I have an answer to your sequence question.

First, the character "A" can be represented as CHR(65), "B" as CHR(66),
and so on.

The answer lies in using two Oracle functions: MOD and TRUNC.
-- MOD gives the remainder of two numbers. For example, MOD(50,26) gives
   24, which is the remainder of 50/26.
-- TRUNC gives just the integer value of a number. So TRUNC(50/26) gives
   1, which is the integer value of 1.92307692...

So, you want a sequence of characters AAA, AAB, AAC, ...

The last character is simply chr(MOD(x,26)+64)
The second-to-last character is chr(MOD(TRUNC(x/26),26)+65)
The third-to-last character is chr(MOD(TRUNC(x/26)/26),26)+65)

Putting it all together:

SELECT chr(MOD(TRUNC(x/26)/26),26)+65)|| chr(MOD(TRUNC(x/26),26)+65)||
chr(MOD(x,26)+64)

You can replace "x" above with SEQUENCE_NAME.NEXTVAL. This is good for a
three-character sequence.

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 310+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://www.arikaplan.com                          <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->


On Fri, 20 Aug 1999, Ryan McAleer wrote:

> Thank you Ari. I guess the most obvious is sometimes the hardest to see. I 
> have another sequence question for you if you don't mind. How would you 
> create a sequence of three letters starting  at AAA, then AAB, AAC, AAD, 
> etc.?
> 
> 
> Ryan,
> 
> I am not sure what you mean by "follows". Do you mean the first number is
> "bogus1" then "bogus2" and "bogus3", etc?
> 
> If so, you can create a sequence and then use a trigger to say
> 
> field_name = 'bogus' || sequence_name.nextval;
> 
> Best regards,
> 
> -Ari Kaplan
> Independent Oracle DBA Consultant
> 
> <-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
> <-> For 310+ Oracle tips, visit my Web Page:                      <->
> <->                                                               <->
> <->             http://www.arikaplan.com                          <->
> <->                                                               <->
> <->             email: akaplan@interaccess.com                    <->
> <-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page h="16" height="18"> Back to Ari Kaplan's Home Page