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;
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 = �.,�;
Explanation
-----------
The Database was running with the NLS_NUMERIC_CHARACTERS = �.,� parameter but
the client was using �,.� .
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;
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 /
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 ...
0
Captive1180Commented:
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.
select to_number(replace(prod_amo
from mytable;
This will replace all ',' with '.' and convert it into number