CODEWITHSUNDEEP
sqloracle-database
Luke
Luke

Reputation: 1258

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?

Upvotes: 1

Views: 3354

Answers (4)

Migs
Migs

Reputation: 1488

Just to add to the answers above, you would need to put in an LPAD function to add Leading Zeros to the number incremented.

DDL and DML:

create table my_table
(
    ID          varchar2(4)
   ,the_field   varchar2(100)
);

insert into my_table (ID, the_field) values(1, '0001');
insert into my_table (ID, the_field) values(2, '0002');
insert into my_table (ID, the_field) values(3, '9998');
insert into my_table (ID, the_field) values(4, '9999');
insert into my_table (ID, the_field) values(5, '0014');
insert into my_table (ID, the_field) values(6, '0903');
insert into my_table (ID, the_field) values(7, '2108');

commit;

Data from my_table:

--- ----------
ID  THE_FIELD
--- ----------
1   0001
2   0002
3   9998
4   9999
5   0014
6   0903
7   2108

THE PL/SQL Block: (P.S. I moved the SELECT..INTO statement to a Cursor so i can loop all the data for this example)

SET SERVEROUTPUT ON;
declare

    new_some_variable varchar2(100);

    Cursor c1 is
    SELECT  id,
            the_field some_variable
    FROM    my_table;

begin

    for c_1 in c1 loop

    DBMS_OUTPUT.PUT_LINE('The ID is: '||c_1.ID);
    DBMS_OUTPUT.PUT_LINE('Value of Some_Variable before Update: '||c_1.some_variable);

        IF (c_1.some_variable = '9999') then    
            new_some_variable := '0001';        
        ELSE

            new_some_variable := LPAD(TO_NUMBER(c_1.some_variable)+1, 4, '000');

        END IF;

    DBMS_OUTPUT.PUT_LINE('New Value of Some_Variable After Update: '||new_some_variable);

    end loop;

END;

Output:

The ID is: 1
Value of Some_Variable before Update: 0001
New Value of Some_Variable After Update: 0002
The ID is: 2
Value of Some_Variable before Update: 0002
New Value of Some_Variable After Update: 0003
The ID is: 3
Value of Some_Variable before Update: 9998
New Value of Some_Variable After Update: 9999
The ID is: 4
Value of Some_Variable before Update: 9999
New Value of Some_Variable After Update: 0001
The ID is: 5
Value of Some_Variable before Update: 0014
New Value of Some_Variable After Update: 0015
The ID is: 6
Value of Some_Variable before Update: 0903
New Value of Some_Variable After Update: 0904
The ID is: 7
Value of Some_Variable before Update: 2108
New Value of Some_Variable After Update: 2109

Hope that Helps!

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191235

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

Upvotes: 3

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

some_variable:= to_char(to_number(some_variable)+1,'FM0000');

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You can use to_char and to_number 

select  TO_CHAR(to_number(my_column, '9999')+1)  from dual

Upvotes: 0

Related Questions