title: Converting string to formatted number in oracle
body: <p>I am using Oracle 10g and have a database with database column  Q1 of datatype varchar2.  This column holds float and int values mostly.</p>
  
  <p>I am trying to run a query that will grab the values, but have the value format in standard US currency format.  For example, if a value is 3020 the query will return 3,020.00.</p>
  
  <p>I have tried the following, but neither work.</p>
  
  <pre><code>SELECT TO_CHAR(Q1, '9,999.99') FROM TABLE1;
  
  ORA-01722: invalid number
  01722. 00000 -  "invalid number"
  
  
  SELECT TO_CHAR(TO_NUMBER(Q1), '9,999.99') FROM TABLE1;
  
  ORA-01722: invalid number
  01722. 00000 -  "invalid number"
  </code></pre>
  
  <p>I also tried using an actual value instead of the column name, and the first example works.  :-/</p>
  
  <pre><code>SELECT TO_CHAR('1234', '9,999.99') FROM TABLE1;  //returns 1,234.00
  </code></pre>
  
  <p>After this step, I went back and tried adding a format mask to TO_NUMBER():
      SELECT TO_CHAR(TO_NUMBER(Q1, '9,999.99'), '9,999.99') FROM TABLE1;</p>
  
  <pre><code>ORA-01722: invalid number
  01722. 00000 -  "invalid number"
  </code></pre>
  
  <p>It's still not working.</p>
  
  <p>Can someone explain exactly why this doesn't work for my column?  Initially though it was because the datatype of the column wasn't number, float, or integer, but even after converting to number I still get the same error.  Any help would be greatly appreciated.</p>