Solved

Problem with to_number()

Posted on 2004-09-22
18
2,447 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
Industry Leaders: 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!

 

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

Industry Leaders: 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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

729 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