[IDS 7.31] convert Char to Numeric

Hi ! I have a numeric value stored in a char row in a table. I need to calculate a sum : select sum(qte * val_num_in_text) from the_table i get an error : Character to numeric conversion error ! how can i convert the text value in numeric, i now about the to_char function but i was not able to find the opposite (to_numeric does not work :-) Many thanks in advance for you help Frederi.

Fred (au boulot) wrote: > I have a numeric value stored in a char row in a table. I need to calculate > a sum : > > select sum(qte * val_num_in_text) > from the_table > > i get an error : Character to numeric conversion error ! > > how can i convert the text value in numeric, i now about the to_char > function but i was not able to find the opposite (to_numeric does not work > :-) It would seem that you've got one or more characters that can't be converted to a numeric value in your character field; you don't need a special function to do a "to_numeric" type of conversion. Unless someone else has another idea, you may simply have to review the data in your 'val_num_in_text' column to find the offending character(s). You'll need to watch for non-printing characters such as tabs as well as the usual alpha characters and so on. -- June Hunt

CREATE TABLE test (col1 CHAR(2)); INSERT INTO test VALUES ("10");SELECT sum (col1 * col1) from test; works OK for me!! (sum) 100.000000000000 "Fred \\(au boulot\\)" <falxirco@wanadoo.fr> wrote in message news:<ccgche$6a0$1@news-reader5.wanadoo.fr>... > Hi ! > > I have a numeric value stored in a char row in a table. I need to calculate > a sum : > > select sum(qte * val_num_in_text) > from the_table > > i get an error : Character to numeric conversion error ! > > how can i convert the text value in numeric, i now about the to_char > function but i was not able to find the opposite (to_numeric does not work > :-) > > Many thanks in advance for you help > > Frederi.