2 Replies Latest reply on Jul 11, 2019 3:04 PM by BluShadow

    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