1 Reply Latest reply: Jan 14, 2013 1:47 AM by Marwim RSS

    How To Avoid Special Character

    970147
      Dear Experts,



      The Input Value : 1,00,000.00

      How To Avoid the Comma (, . 00) and Any Spl Character...

      How To check Please advice me ...

      Thanks in Advvance

      Kumar
        • 1. Re: How To Avoid Special Character
          Marwim
          Hello Kumar,

          these special characters are decimal spearator and group separator.
          Where do you want to avoid the special characters?
          When the data is displayed in a select?

          The display format depends on the settings of your database and your tool.
          select * from nls_session_parameters
          where parameter = 'NLS_NUMERIC_CHARACTERS';
          
          PARAMETER                      VALUE   
          ------------------------------ --------
          NLS_NUMERIC_CHARACTERS         .,
          You can control the output format by setting these session parameters or by using TO_CHAR together with a format string.

          By default a simple select does not display a group separator and a decimal separator only when necessary
          SELECT 1000000 num_val FROM dual;
          
             NUM_VAL
          ----------
             1000000 
          
          SELECT 1000001/100 num_val FROM dual;
          
             NUM_VAL
          ----------
            10000.01
          
          SELECT TO_CHAR(1000001/100) char_val from dual;
          
          CHAR_VAL
          --------
          10000.01
          But you can define the output by passing a format string in TO_CHAR
          SELECT TO_CHAR(1000001/100,'999G999G990D99') char_val from dual;
          
          CHAR_VAL      
          ---------------
                10,000.01
          You can even add a currency symbol
          SELECT TO_CHAR(1000001/100,'999G999G990D99L') char_val FROM dual;
          
          CHAR_VAL                
          -------------------------
                         10,000.01$
          And you can define the separator characters
          SELECT TO_CHAR(1000001/100,'999G999G990D99','NLS_NUMERIC_CHARACTERS=''.,''') char_val FROM dual;
          
          CHAR_VAL      
          ---------------
                10,000.01
          So it depends on the settings of the tool that you use to display the data and your query.

          Regards
          Marcus