title: how to convert varchar2 to number in oracle?
body: <p>I have a table with many records and it continues to grow:</p>
  
  <pre><code>  table T_A
    { 
      total varchar2(10),
      total number
    }
  </code></pre>
  
  <p>The "total" field composed with 5 numbers and 1 character, e.g. "12345p", the character is either "p" or "N". </p>
  
  <p>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:</p>
  
  <blockquote>
    <p>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".</p>
  </blockquote>
  
  <p>How to write this trigger?</p>