Home » SQL & PL/SQL » SQL & PL/SQL » Updating a varchar2 field (Oracle 8i)
Updating a varchar2 field [message #352546] Wed, 08 October 2008 06:29 Go to next message
Derek N
Messages: 80
Registered: September 2002
Member
Hi

I have a table with a field, list_price varchar2(16).

This field is populated from a text file. After loading the table I want to update this field ie.
update ms_price
set price = price * 20

As this is a varchar2 field I get an error,'ORA-01722:Invalid number'

Is there anyway around this without having to alter the table and change the field to a numeric field.

Regds

Re: Updating a varchar2 field [message #352550 is a reply to message #352546] Wed, 08 October 2008 06:36 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

Convert varchar2 fields to number
update ms_price
set price = to_number(price) * 20

Re: Updating a varchar2 field [message #352552 is a reply to message #352546] Wed, 08 October 2008 06:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can't change to a numeric field as there are data that are not numeric.

In addtion, your question is meaningless, reread it; "without having to alter the table and change the field to a numeric field" which means "can we alter without altering".

Regards
Michel
Re: Updating a varchar2 field [message #352580 is a reply to message #352546] Wed, 08 October 2008 07:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could write an Is_Number function, like this:
CREATE OR REPLACE FUNCTION IS_NUMBER (p_in  in  varchar2) return number as
  v_num  number;
BEGIN
  v_num := to_number(p_in);
  return 1;
EXCEPTION
  WHEN others THEN
    return 0;
END;
/

And change your Dml to
update ms_price
set    price = price * 20
where  is_number(price) = 1;

But that's a pretty poor solution.
Much better to clean the data and change to datatype to Number.
Re: Updating a varchar2 field [message #352581 is a reply to message #352550] Wed, 08 October 2008 07:53 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
misragopal wrote on Wed, 08 October 2008 07:36
Convert varchar2 fields to number
update ms_price
set price = to_number(price) * 20




This is still no good. What you are doing here is converting a string to a number and multiplying it by 20. This is ok. but then you are relying on implicit conversion by putting a number into a varchar column. Most likely it will work, but not clean code.
Re: Updating a varchar2 field [message #352625 is a reply to message #352546] Wed, 08 October 2008 11:14 Go to previous messageGo to next message
shrad2001
Messages: 20
Registered: September 2008
Location: faridabad
Junior Member
Hi
you can easily update table without any conversion

i have tried by creating table having varchar2 datatye and then i update data ,i did it easily

so i think you are asking wrong question

thanks
smily
Re: Updating a varchar2 field [message #352632 is a reply to message #352625] Wed, 08 October 2008 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The problem is:
Quote:
As this is a varchar2 field I get an error,'ORA-01722:Invalid number'

What is the result of "20 * Michel"?

Regards
Michel
Re: Updating a varchar2 field [message #352649 is a reply to message #352546] Wed, 08 October 2008 15:23 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Derek N wrote on Wed, 08 October 2008 13:29
I have a table with a field, list_price varchar2(16). This field is populated from a text file. After loading the table I want to update this field ie.
update ms_price set price = price * 20 


How about multiplying the price with 20 during the loading phase (SQL*Loader?). Invalid records (as "Michel * 20") would be rejected anyway. You'd do the job in a single step, not two steps (load + update).
Previous Topic: Where Like or In
Next Topic: help in SQL query to find the required row
Goto Forum:
  


Current Time: Sat Feb 15 01:26:45 CST 2025