8 Replies Latest reply: Dec 14, 2012 2:03 AM by theoa RSS

    Invalid number ....

    923195
      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
          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
            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
              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
                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
                  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
                    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 ....
                      523861
                      this may depend on your nls_language parameter.

                      different countries use different separators for decimal places.
                      • 8. Re: Invalid number ....
                        theoa
                        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