Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Unanswered: Drop Trailing Decimal Zeros in SQL Plus SELECT

    I am new to SQL and would like to know if numeric values like $25.30 can be displayed from a SELECT statement as $25.3, or $50.00 would be displayed as $50

    I've looked at COLUMN column_name FORMAT and have not found a code, if there is one, to not display trailing zeros in a field displayed as currency with 2 decimals.

    Yes! This is SQL *Plus version 9.2 homework and I am searching for the answer.

    Thanks.
    Jerry


    you may want to ask this question in the oracle forum

    mysql can do it, but mysql cannot run SQL*Plus
    __________________
    rudy r937.com Ask the Expert

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You, actually, don't have to do anything to display values in such a way ...
    Code:
    > create table exam (val number);
    Table created.
    > insert into exam values (25.30);
    1 row created.
    > select * from exam;
           VAL
    ----------
          25.3
    However, you may use COLUMN <col_name> FORMAT to specify the output of your select statement. For example, putting
    Code:
    > column val format $999.99
    > select * from exam;
           VAL
    ----------
        $25.30
    > column val format $999.9
    > select * From exam;
           VAL
    ----------
         $25.3
    Read something more about column formats here

    Furthermore, you could use ROUND and TRUNC functions to achieve something like you're required to:
    Code:
    > select round(val) from exam;
           VAL
    ----------
           $25
    > select round(val, 1) from exam;
           VAL
    ----------
         $25.3
    > select trunc(val) from exam;
           VAL
    ----------
           $25
    Of course, those functions will NOT always show result you'll be happy with.

    Or, you could use SUBSTR along with the INSTR functions to extract part of the column value and then format it using TO_CHAR function. Something like
    Code:
    > select to_char(substr(val, 1, instr(val, '.', 1) - 1), '$99.99') result from exam;
    RESULT
    -------
     $25.00
    I hope some of those things will help you.

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Thanks for your reply. As a SQL newbie, I will try each of the functions you described and become familiar with them as used with SQL.

    I believe that given enough time, you can figure out anything, which I did. My solution to drop trailing zeros in two decimals and display the field with a leading dollar sign is this:
    SELECT customer_id, ' $'||service_charge AS "Service Charge"
    FROM cust_charge;

    So my solution is to concatenate the dollar sign to a field with the datatype of NUMBER(6,2).

    Jerry

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If you're happy with it, I'm even happier
    Enjoy in SQL!

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    column service_charge format fm$99,999.999
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •