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?

APC · Accepted Answer · 2011-08-13 16:04:26Z

6

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;

edited Aug 13, 2011 at 16:04

APC

147k20 gold badges180 silver badges289 bronze badges

answered Aug 13, 2011 at 15:43

Kevin Burton

12k2 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Randy Over a year ago

good for structure... OP may wish to do other checks if his data is dirtier than he thinks.. :)

APC Over a year ago

Note that the OP's posted code has a lower case p and an upper case N: the instr() checks need to match the case.

Rob van Wijk Over a year ago

+1 A subjective sidenote though: I find "IF :NEW.total like '%p%' THEN" much more in line with your intentions than using the INSTR operator.

Tom Jacky Over a year ago

It's exactly what I wanted! Thanks everybody.

Collectives™ on Stack Overflow

how to convert varchar2 to number in oracle?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related