This discussion is archived
1 Reply Latest reply: Jan 13, 2013 11:47 PM by Marwim RSS

How To Avoid Special Character

970147 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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

Legend

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