Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2014
    Posts
    3

    Unanswered: Remove trailing zeros after decimal point in DB2 in char column

    have the following values in label table in DB2 (version : 9.5.8)
    select field4 from lablel with ur
    1.5000
    0.006
    9.0001
    104.2500
    17.0000
    3.5000

    Is this possible to eliminate the trailing zero's after the decimal point by a update query in DB2, if all the values are zeros's after the decimal point i need to keep that as .0

    Excepted output:
    1.5
    0.006
    9.0001
    104.25
    17.0
    3.5

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try to use one of these expressions in your update query.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     lablel(field4) AS (
    VALUES
      ( '1.5000'   )
    , ( '0.006'    )
    , ( '9.0001'   )
    , ( '104.2500' )
    , ( '17.0000'  )
    , ( '3.5000'   )
    , ( '0.0000'   )
    , ( '00.0000'  )
    )
    SELECT field4
         , REPLACE(TRIM(T '0' FROM REPLACE(field4 , '.0' , '.X')) , '.X' , '.0') AS exp_1
         , REPLACE(TRIM(T '0' FROM field4) || ' ' , '. ' , '.0')                 AS exp_2
     FROM  lablel
    ;
    ------------------------------------------------------------------------------
    
    FIELD4   EXP_1    EXP_2    
    -------- -------- ---------
    1.5000   1.5      1.5      
    0.006    0.006    0.006    
    9.0001   9.0001   9.0001   
    104.2500 104.25   104.25   
    17.0000  17.0     17.0     
    3.5000   3.5      3.5      
    0.0000   0.0      0.0      
    00.0000  00.0     00.0     
    
      8 record(s) selected.
    Note: If field4 include trailing blank(s), replace "field4" by "RTRIM(field4)".
    Last edited by tonkuma; 06-04-14 at 21:02. Reason: Add Note.

  3. #3
    Join Date
    Jun 2014
    Posts
    3

    Hi Tonkuma the queries are not working

    db2 => select field4, REPLACE(TRIM(T '0' FROM field4) || ' ' , '. ' , '.0') from label

    field4 2
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    140.0000 140.0000

    1 record(s) selected.

    db2 => select field4, REPLACE(TRIM(T '0' FROM REPLACE(field4 , '.0' , '.X')) , '.X' , '.0') from label

    field4 2
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    140.0000 140.0000

    1 record(s) selected.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please consider my Note
    Note: If field4 include trailing blank(s), replace "field4" by "RTRIM(field4)".
    If that might be not applicable,
    see the intermediate results step by step, like...
    select field4 || ';', TRIM(T '0' FROM field4) || ';' from label
    select field4 || ';', TRIM(T '0' FROM field4) || ' ' || ';' from label
    ...

  5. #5
    Join Date
    Jun 2014
    Posts
    3

    My DB 2 version is 9.5

    yes. i already tried that. My DB 2 version is 9.5

    STILL NOT WORKING

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by ganz3110 View Post
    yes. i already tried that. My DB 2 version is 9.5

    STILL NOT WORKING
    Please publish your tried queries and the resutls you got.

Tags for this Thread

Posting Permissions

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