Solved

Problem with to_number()

Posted on 2004-09-22
18
2,446 Views
Last Modified: 2007-12-19
Hi,

I have a table with a column (prod_amount) which is a varchar2.

Prod_amount:
123.23
45678
312,78

As you see there is both used , and . as decimal seperator.

When I:
  select to_number(prod_amount) as mynum;

I get an error "Invalid number"

What is the best way to solve my problem ? I can't replace the , to . in the database.


//akj
0
Comment
Question by:dk_akj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +8
18 Comments
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 125 total points
ID: 12121003
try,

select to_number(replace(prod_amount,',','.')) as prod_amount
from mytable;


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

Expert Comment

by:andrewst
ID: 12121072
...and then remember NEVER AGAIN to use a VARCHAR2 column to store numeric data!
0
 

Author Comment

by:dk_akj
ID: 12121159
Thanks.

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

//Akj
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

by:dk_akj
ID: 12121175
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
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12121209
use

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

Expert Comment

by:catchmeifuwant
ID: 12121225
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.
0
 
LVL 13

Expert Comment

by:Murali Murugesan
ID: 12121235
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*
0
 
LVL 9

Expert Comment

by:konektor
ID: 12121487
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;
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 12121591
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 12121601
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; .
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 12121614
SLQ> ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
Explanation
-----------
The Database was running with the NLS_NUMERIC_CHARACTERS = '.,' parameter but
the client was using ',.'.
0
 
LVL 9

Expert Comment

by:pratikroy
ID: 12121678
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 !
0
 
LVL 13

Expert Comment

by:riazpk
ID: 12121737
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
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12121812
do you really need to replace the "," or "."?

did you try this?

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

Expert Comment

by:Jankovsky
ID: 12122359

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
 

Expert Comment

by:Captive1180
ID: 12131729
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..
0
 

Author Comment

by:dk_akj
ID: 12131765
Hi all.

Thanks for your help.

I solved my problem with the replace function.

//akj
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12131793
dk_akj,

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question