varcharseq

create or replace function varcharseq
DECLARE
  my_string VARCHAR2(50);
  string_portion VARCHAR2(50);
  number_portion VARCHAR2(50);
  the_number NUMBER;
  string_location NUMBER;
BEGIN
  /* define the string */
  my_string := 'abcd_ver2';
  /* find where the number is */
  /* use the fact that INSTR returns zero */
  /* if not found */

string_location:=MIN(DECODE(INSTR(my_string,'0'),0,999,INSTR
(my_string,'0')),DECODE(INSTR(my_string,'1'),0,999,INSTR(my_string
('1')),....,DECODE(INSTR(my_string,'9'),0,999,INSTR(my_string,'9')));

  /* now that we know where the number is, */
  /* get the string and number portions */

string_portion:=SUBSTR(my_string,1,string_location-1);
  number_portion:=SUBSTR(my_string,string_location);
  /* Convert the number to NUMBER datatype */
  /* and increment it */
  the_number:=TO_NUMBER(number_portion);
  the_number:=the_number+1;
  /* Place the number back on the string */
  my_string := string_portion || TO_CHAR(the_number);
END;
/