Problem solve Get help with specific problems with your technologies, process and projects.

Generating a 10 character alphanumeric sequence

I need to create a 10 character alphanumeric sequence for a client. The sequence is to be static like this: a111a11a11a. If it's a number, increment 1 until you reach 9, then move to the next column, if it's a letter, increment 1 until z, then move to the next column. Can you suggest a simple solution for this?

Try the following function that utilizes the built-in functions ASCII and CHR. I have created the function for...

an arbitrary length of sequence. However, the function will not attempt to exceed the maximum length of the string. It the original sequence is 'a111a11a11a'(in your example the length is 10) the maximum the function will return is 'z999z99z99z'. Also, I have also tested the boundary conditions of a single character sequence of 9 and z.

So, with much further ado, here is the function:

create or replace function return_next_seq (curr_sequence IN OUT VARCHAR2) 
return varchar2 is

 retval VARCHAR2(4000) := null;

 eval_digit CHAR(1) := null;

 original_sequence VARCHAR2(4000) := curr_sequence;


  for j in REVERSE 1..length(curr_sequence) loop  -- Using reverse to know 
        -- the exact digit position

     eval_digit := substr(curr_sequence, length(curr_sequence));

     IF (ASCII(eval_digit) between 49 and 56) OR 
        (ASCII(eval_digit) between 97 and 121) THEN     
       eval_digit := CHR(ASCII(eval_digit) +1);
       curr_sequence := substr(curr_sequence,1,length(curr_sequence)-1);
       retval := curr_sequence || eval_digit || substr(original_sequence, 
length(curr_sequence || eval_digit)+1);

     ELSE  -- move to the next digit leaving the evaluated digit untouched.

     curr_sequence := substr(curr_sequence,1,length(curr_sequence)-1);
     END IF;   
  end loop;
  IF retval is null THEN
    return 'MAXIMUM value of sequence reached';
  return retval;

This was last published in April 2005

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.