Remove trailing zeroes...immediate

prem18 · #1 (**permalink**) 11-06-08, 16:53

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

Peter.Vanroose · #2 (**permalink**) 11-06-08, 17:41

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.

umayer · #3 (**permalink**) 11-07-08, 01:43

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;

Peter.Vanroose · #4 (**permalink**) 11-07-08, 02:14

Quote:

Originally Posted by umayer

Consider using STRIP (string, T , '0' ) to remove trailing zeros

This would't remove the trailing "." (if any), though.

nick.ncs · #5 (**permalink**) 11-07-08, 02:18

If your number is going to only of 4 digits always, then you can try this as well...

Quote:

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

UPDATE-

Modified tonkuma's query

Quote:

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

tonkuma · #6 (**permalink**) 11-07-08, 02:21

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.

prem18 · #7 (**permalink**) 11-07-08, 09:01

Working fine . Thank u ........

Strip is not working here. ( DB2 version 8 ).

tonkuma · #8 (**permalink**) 11-08-08, 01:56

Quote:

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.

stegau · #9 (**permalink**) 03-12-09, 11:40

another solution...

col1 is DEC(4,2)

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

Internet Services

Web Development

Digital Marketing

Consumer Tech