Problem with to_number()

...and then remember NEVER AGAIN to use a VARCHAR2 column to store numeric data!

Thanks.

It's not me who have maked the table :-)  

//Akj

Hmm.. I still get an error.

I guess something else is messed up to..

Any tricks to find the row(s) with bad data ??

Select * from mytable where lower(prod_amount) <> upper(prod_amount) gives no records.

//akj

use

select *
FROM mytable
where translate(prod_amount,'0123456789',' ')  is not null

ok..since you consider . & , as valid, you might have to change it

select *
FROM mytable
where translate(prod_amount,'0123456789.,',' ')  is not null

--Note the space between the quotes ' ' in translate 3rd parameter.

try this,

select to_number(replace(prod_amount,',','.')) as amount  From mytable where lower(prod_amount)=upper(prod_amount).

This takes only the rows with numwric value and then converts it into numbers.

Hope this helps u.

-Murali*

u can create function :

create Function char2num
  (A Varchar2) RETURN Number  IS
   N Number;
BEGIN
   N:=TO_NUMBER('9.9');
   Return TO_NUMBER(REPLACE(A,',','.'));
EXCEPTION
   When OTHERS then
   Return TO_NUMBER(REPLACE(A,'.',','));
END;

than u can use it in select statements :

select char2num(prod_amount) as mynum;

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';

From Metalink:
Problem Description
-------------------
Running the same query, in different clients, generates the oracle error
ORA-1722 in one of them.
Error: ORA 1722
Text: invalid number
------------------------------------------------------------------------
// *Cause: The attempted conversion of a character string to a number failed
// because the character string was not a valid numeric literal. Only numeric
// fields or character fields containing numeric data may be used in arithmetic
// functions or expressions. Only numeric fields may be added to or subtracted
// from dates.
// *Action: Check the character strings in the function or expression. Check
// that they contain only numbers, a sign, a decimal point, and the character
// "E" or "e" and retry the operation.
Solution Description
--------------------
SLQ> ALTER SESSION SET NLS_NUMERIC_CHARACTERS = &#65533;.,&#65533;;
Explanation
-----------
The Database was running with the NLS_NUMERIC_CHARACTERS = &#65533;.,&#65533; parameter but
the client was using &#65533;,.&#65533; .

SLQ> ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
Explanation
-----------
The Database was running with the NLS_NUMERIC_CHARACTERS = '.,' parameter but
the client was using ',.'.

Well, have you checked the NLS_NUMERIC_CHARACTERS in your DB session ?

Have a look at the following :
SQL> select * from tab_nums;

         A
----------
     10.34
   4557.65
3467846.45

SQL> alter session set NLS_NUMERIC_CHARACTERS=',.';

Session altered.

SQL> select * from tab_nums;

         A
----------
     10,34
   4557,65
3467846,45

SQL> alter session set NLS_NUMERIC_CHARACTERS='.,';

Session altered.

SQL> select * from tab_nums;

         A
----------
     10.34
   4557.65
3467846.45

Now, if you change the VARCHAR2 datatype to number, you will have to keep in mind whether the decimal separator is set to ',' or '.'.

The comma as a decimal separator is used in several continental European countries, including Germany, Austria, Switzerland, Belgium, Luxembourg, and I think - also Italy and the Netherlands.

Based on whatever the default NLS_NUMERIC_CHARACTERS in your DB, you will have to replace the comma or decimal respectively.

As catchmeifyouwant has mentioned in his post above :
select to_number(replace(prod_amount,',','.')) as prod_amount
from mytable;

This will replace all ',' with '.' and convert it into number

You can use this if your NLS_NUMERIC_CHARACTERS is set to '.,'

If its the other one, then try and replace the dots with commas instead by :
select to_number(replace(prod_amount,'.',',')) as prod_amount
from mytable;

Hope this will help !

SQL> select * from t1;

A
==========
123.23
45678
312,78

Elapsed: 00:00:00.50
SQL> desc t1
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- ------------
 A                                                                                            VARCHAR2(10)

SQL> select to_number(replace(a,',','.'),'9999999.99') from t1
  2  /

TO_NUMBER(REPLACE(A,',','.'),'
==============================
                        123.23
                         45678
                        312.78

do you really need to replace the "," or "."?

did you try this?

select to_number(prod_amount, '999,999,999.99') from <table_name>;

select  case when instr(prod_amount,',')>0 then to_number( prod_amount,'STM','NLS_NUMERIC_CHARACTERS='', ''') else to_number( prod_amount,'STM','NLS_NUMERIC_CHARACTERS=''. ''') end as mynum from ...

hi  dk,

Pls try this and c if it solves your problem

select to_number(prod_amount,'9999,99.99') from tablename

I tried this : I have a value like 40,00.00 in my varchar2 column.

When i used the select clause as shown above , i've got the answer as --- 4000

The last statement you have made is not very clear. You have mentioned ,that you cannot replace the , to . in the database.

what you are trying to do is execute a select statement ,which in anyways won't change the data in your table.

In case I have got this meaning wrong , do clarify.

Hope this solution helps you..

Hi all.

Thanks for your help.

I solved my problem with the replace function.

//akj

dk_akj,

Why a Grade of B?Did it not solve your problem?