CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Bad Programmer
Bad Programmer

Reputation: 3752

Converting string to formatted number in oracle

I am using Oracle 10g and have a database with database column Q1 of datatype varchar2. This column holds float and int values mostly.

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.

I have tried the following, but neither work.

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"

I also tried using an actual value instead of the column name, and the first example works. :-/

SELECT TO_CHAR('1234', '9,999.99') FROM TABLE1;  //returns 1,234.00

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;

ORA-01722: invalid number
01722. 00000 -  "invalid number"

It's still not working.

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.

Upvotes: 0

Views: 4568

Answers (2)

peterm
peterm

Reputation: 92785

That most likely means that you have nonnumerical values in Q1.

Therefore either filter out rows with such values or substitute them (e.g. with 0.00)

SELECT TO_CHAR(Q1, '9,999.99') 
  FROM table1
 WHERE REGEXP_LIKE(q1, '^[+-]?[.0-9]+$');

SELECT CASE WHEN REGEXP_LIKE(q1, '^[+-]?[.0-9]+$') 
            THEN TO_CHAR(Q1, '9,999.99') 
            ELSE '0.00'
       END q1
  FROM table1;

Here is SQLFiddle demo

Upvotes: 1

Patrick Marchand
Patrick Marchand

Reputation: 3445

Your Q1 column is VARCHAR2? You'll want to do something like this then: 

select to_char(to_number('3200'), '9,999.00') from dual

or using your table/column references:

select to_char(to_number(Q1), '9,999.00') from table1;

You just have to hope that this column holds only numbers and no letters, special characters, etc. or else you'll get the

ORA-01722: invalid number

error again.

Upvotes: 2

Related Questions