Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2007
    Posts
    63

    Unanswered: Remove trailing zeroes...immediate

    HI,,
    please find my issue which im facing today--

    Requirement:

    It deals with a single field.

    1. Divide a 4 digit number say 'X' (of type Integer) by 100.

    -- This can be done by type casting it to decimal

    2. I have to remove all the trailing zeroes from the number

    -- I tried the below code:


    SELECT RTRIM(REPLACE(CHAR(cast(2459 as decimal)/100) ,'0','')) FROM SYSIBM.SYSDUMMY1;

    It works fine with a non zero number.
    when i try with 2010 in the place of green marked integer, it replaces inner zero also( ofcourse, it should do as per the code ...!!)
    Also, when i try with a zero alone in the place of green marked integer, it fails as

    it displays a decimal alone



    I require the code should work for both zero and non zero numbers.
    Please advise

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Code:
    SELECT REPLACE(SUBSTR(CAST(2459 as CHAR(4)),1,2)||'.'||
                   SUBSTR(CAST(2459 as CHAR(4)),3,2),'.00','')
    FROM SYSIBM.SYSDUMMY1
    And add additional replaces for '.10' -> '.1' etc.
    Last edited by Peter.Vanroose; 11-06-08 at 18:54.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    Consider using STRIP (string, T , '0' ) to remove trailing zeros

    eg:
    SELECT STRIP (CHAR(CAST(2459 AS DECIMAL)/100),T,'0' ) FROM
    SYSIBM.SYSDUMMY1

    Or a query like this:


    SELECT STRIP ( LTRIM(CHAR(CAST(2459 AS DECIMAL(15,2))/100)) ,L,'0' ) FROM
    SYSIBM.SYSDUMMY1;

    or this:

    SELECT LEFT ( DIGITS( CAST(2459 AS DECIMAL(4)) ) ,2 ) !! '.' !!
    RIGHT( DIGITS( CAST(2459 AS DECIMAL(4)) ) ,2 )
    FROM SYSIBM.SYSDUMMY1;
    Last edited by umayer; 11-07-08 at 02:58.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by umayer
    Consider using STRIP (string, T , '0' ) to remove trailing zeros
    This would't remove the trailing "." (if any), though.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    If your number is going to only of 4 digits always, then you can try this as well...

    SELECT substr(char(cast(2010 as decimal)/100),4,5) FROM SYSIBM.SYSDUMMY1
    UPDATE-

    Modified tonkuma's query
    SELECT SUBSTR(CHAR(CAST(X AS DECIMAL)/100),4,5) FROM (VALUES 2459, 2010, 2000, 2001, 0, 1, 10, 100) X(X)

    24.59
    20.10
    20.00
    20.01
    00.00
    00.01
    00.10
    01.00
    Last edited by nick.ncs; 11-07-08 at 04:33.
    IBM Certified Database Associate, DB2 9 for LUW

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT x
         , TRANSLATE(STRIP(RTRIM(CAST(REPLACE(CHAR(CAST(x AS DECIMAL(7,2))/100),'0.','x.') AS VARCHAR(50))),B,'0'),'0','x')
      FROM (VALUES 2459, 2010, 2000, 2001, 0, 1, 10, 100) x(x)
    ;
    ------------------------------------------------------------------------------
    
    X           2                                                 
    ----------- --------------------------------------------------
           2459 24.59                                             
           2010 20.1                                              
           2000 20.                                               
           2001 20.01                                             
              0 0.                                                
              1 0.01                                              
             10 0.1                                               
            100 1.                                                
    
      8 record(s) selected.

  7. #7
    Join Date
    Apr 2007
    Posts
    63

    Thumbs up

    Working fine . Thank u ........
    Strip is not working here. ( DB2 version 8 ).

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Strip is not working here. ( DB2 version 8 ).
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT x
         , TRANSLATE(LTRIM(RTRIM(TRANSLATE(CAST(REPLACE(CHAR(CAST(x AS DECIMAL(7,2))/100),'0.','x.') AS VARCHAR(50)),' ','0'))),'00',' x')
      FROM (VALUES 2459, 2010, 2000, 2001, 0, 1, 10, 100) x(x)
    ;
    ------------------------------------------------------------------------------
    
    X           2                                                 
    ----------- --------------------------------------------------
           2459 24.59                                             
           2010 20.1                                              
           2000 20.                                               
           2001 20.01                                             
              0 0.                                                
              1 0.01                                              
             10 0.1                                               
            100 1.                                                
    
      8 record(s) selected.

  9. #9
    Join Date
    Mar 2009
    Posts
    1

    Another solution ...

    another solution...

    col1 is DEC(4,2)

    select replace(replace(replace(replace(' '||char(col1),' 0000','0'),' 000',''),' 00',''),' 0','')
    from table

Posting Permissions

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