Ask the Expert

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?

    Requires Free Membership to View

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 first published in April 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: