how to convert varchar2 to number in oracle?

Question

I have a table with many records and it continues to grow:

  table T_A
  { 
    total varchar2(10),
    total number
  }

The "total" field composed with 5 numbers and 1 character, e.g. "12345p", the character is either "p" or "N".

Now, I want to write a trigger to convert existing "total" to numbers and store them in "total_num". furthermore, if there are inserting or updating operations, it can automatically finishes this conversion, and it must satisfy the following condition:

if the character is "p", the number is positive, e.g. "12345p" converts to "12345", otherwise, the number is negative. e.g. "12345N" converts to "-12345".

How to write this trigger?

Answer 1

Try this

(not tested as I don't have an oracle connection at the moment)

  CREATE OR REPLACE TRIGGER trg_ta_totals
  BEFORE INSERT OR UPDATE ON T_A
  FOR EACH ROW

    DECLARE
        vnum number;
    BEGIN

    IF instr(:NEW.total,'p') > 0 THEN
       vnum:=TO_NUMBER(SUBSTR(:NEW.total,0,LENGTH(:NEW.total)-1));     
    ELSIF instr(:NEW.total,'N') > 0 THEN
       vnum:=TO_NUMBER(SUBSTR(:NEW.total,0,LENGTH(:NEW.total)-1))*-1;
    END IF; 

    :NEW.total_num := vnum; 

    END;