Luke Luke - 1 month ago 14
SQL Question

Increment varchar value in oracle

I'm writing an Oracle function where I'm trying to increment a varchar(4) value as if it were a number, and roll it over back to start if it maxes out.
So if the previous value is 0001, I set it to 0002. If it's 9999 set it to 0001.

SELECT the_field INTO some_variable
FROM my_table
WHERE ID = <id I use>;

IF (some_variable= '9999')
some_variable:= '0001';
ELSE
--Not sure here--
END IF;


What do I do in the ELSE statement to increment?

Answer

This does seem like a strange thing to want, not least because it seems to reply on a single person modifying the data at a time - if you are updating the row for that ID at some point you could select for update to lock it I suppose.

But assuming you want to stick to this model, you could use mod() to do the rollover:

select to_char(mod(to_number(the_field, '9999') + 1, 10000), 'FM0000')
from my_table
where id = some_id;

But that gives you 0000 as the next values after 9999. If you want to skip that and go to 0001 you could add a greatest() call:

select to_char(greatest(mod(to_number(the_field, '9999') + 1, 10000), 1), 'FM0000')
from my_table
where id = some_id;

Quick demo with some sample data:

create table my_table (id number, the_field varchar2(4));
insert into my_table values (1, '0001');
insert into my_table values (2, '0002');
insert into my_table values (3, '9998');
insert into my_table values (4, '9999');

select id, the_field,
  to_char(greatest(mod(to_number(the_field, '9999') + 1, 10000), 1), 'FM0000')
from my_table;

        ID THE_ TO_CH
---------- ---- -----
         1 0001 0002 
         2 0002 0003 
         3 9998 9999 
         4 9999 0001