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