Q

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;

begin

  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);
       exit;

     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';
  END IF;
  return retval;
end;
/

This was first published in April 2005

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close