Amount Conversion

Ram venu

    Hi ,

     

    I have amount column with  values 1238.78

     

    I want to format amount column into 3 digits comma separated with decimal values ,

     

    TO_CHAR(NVL(AMOUNT,0), 'FM99G999G999G999.00') ,

     

    I want output as $1,247,854.78

     

    please help

      • 1. Re: Amount Conversion
        Paulzip

        Your format model is almost there, but you probably want

         

        'FML99G999G999G990D00'

         

        So your decimal separator isn't hardcoded, it uses the NLS currency symbol and the first significant digit is populated.

         

        I wouldn't NVL amount though - null is NOT zero!

        • 2. Re: Amount Conversion
          BluShadow

          Just remember that formatting is for display purposes, so when you talk about formatting the amount 'column' you are not doing that on the database table column itself, but only in the displayed data when you query it.  The data on the database should always be of an appropriate number datatype, and the formatting on the table itself is irrelevant to how you choose to display it.

           

          Feel free to take a read of the community document: PL/SQL 101 : DataTypes - NUMBER