Updating a varchar2 field [message #352546] |
Wed, 08 October 2008 06:29  |
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 #352580 is a reply to message #352546] |
Wed, 08 October 2008 07:52   |
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   |
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   |
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 #352649 is a reply to message #352546] |
Wed, 08 October 2008 15:23  |
 |
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).
|
|
|