This discussion is archived
8 Replies Latest reply: Dec 14, 2012 12:03 AM by theoa RSS

Invalid number ....

923195 Newbie
Currently Being Moderated
Hello,

i am having a very anoying poblem.

I get varchar2 (which in fact is a number) from XML parsing. That string is like for example 5000.12 string.

Now i want to convert that to a number type. So i made this simple update:
UPDATE test_table

SET value_number = to_number(REPLACE(value_varchar,'.',','));
Error i get is "Invalid number".

Can someone help me?

Thank you!
  • 1. Re: Invalid number ....
    sb92075 Guru
    Currently Being Moderated
    The Cain wrote:
    Hello,

    i am having a very anoying poblem.

    I get varchar2 (which in fact is a number) from XML parsing. That string is like for example 5000.12 string.

    Now i want to convert that to a number type. So i made this simple update:
    UPDATE test_table
    
    SET value_number = to_number(REPLACE(value_varchar,'.',','));
    Error i get is "Invalid number".

    Can someone help me?

    Thank you!
    >
    Hello,

    i am having a very anoying poblem.

    I get varchar2 (which in fact is a number) from XML parsing. That string is like for example 5000.12 string.

    Now i want to convert that to a number type. So i made this simple update:
    UPDATE test_table
    
    SET value_number = to_number(REPLACE(value_varchar,'.',','));
    Error i get is "Invalid number".

    Can someone help me?

    Thank you!
    believe that Oracle accurately reports error reality
  • 2. Re: Invalid number ....
    Niket Kumar Pro
    Currently Being Moderated
    SET value_number = to_number(REPLACE(value_varchar,'.',','));
    change above to below and check....
    SET value_number = to_number(REPLACE(value_varchar,',','.'));
  • 3. Re: Invalid number ....
    923195 Newbie
    Currently Being Moderated
    Hello Niket Kumar,
    SET value_number = to_number(REPLACE(value_varchar,',','.'));
    I think this wont work since in string i get is '.' and not ','. So i am replacing "dot".
  • 4. Re: Invalid number ....
    user639304 Explorer
    Currently Being Moderated
    Hi,

    You don't have to replace . with ,

    Just use
    UPDATE test_table 
    SET value_number = to_number(value_varchar);
  • 5. Re: Invalid number ....
    923195 Newbie
    Currently Being Moderated
    Ok i fixed it. I have put update on after insert trigger on a table and it works (no idea why).

    Thank you all!
  • 6. Re: Invalid number ....
    923195 Newbie
    Currently Being Moderated
    The solution to convert it like that worked only on my local PC ... when customer tried it on his PC (using forms6 it threw an error mentioned above).

    Thank you for you suggestion.
  • 7. Re: Invalid number ....
    WhiteHat Expert
    Currently Being Moderated
    this may depend on your nls_language parameter.

    different countries use different separators for decimal places.
  • 8. Re: Invalid number ....
    theoa Pro
    Currently Being Moderated
    As WhiteHat mentioned, the difference is due to the nls language setting in the client.
    To make it nls independent, use the nls_numeric_characters parameter in to_number, like this
    sql> select to_number('500.12', '9999999D99', 'NLS_NUMERIC_CHARACTERS=''.,''') a from dual;
    
             A
    ----------
        500.12

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points