Converting string to formatted number in oracle

Question

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.

Patrick Marchand · Accepted Answer · 2013-08-27 20:56:41Z

2

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.

answered Aug 27, 2013 at 20:56

Patrick Marchand

3,45523 silver badges13 bronze badges

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

Comments

peterm · Accepted Answer · 2013-08-27 20:59:27Z

1

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

answered Aug 27, 2013 at 20:59

peterm

93k16 gold badges157 silver badges162 bronze badges

Collectives™ on Stack Overflow

Converting string to formatted number in oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related