1. Registered User
Join Date
Apr 2007
Posts
63

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.

2. Registered User
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```
Last edited by Peter.Vanroose; 11-06-08 at 17:54.

3. Registered User
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 01:58.

4. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by umayer
Consider using STRIP (string, T , '0' ) to remove trailing zeros
This would't remove the trailing "." (if any), though.

5. Registered User
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 03:33.

6. Registered User
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. Registered User
Join Date
Apr 2007
Posts
63
Working fine . Thank u ........
Strip is not working here. ( DB2 version 8 ).

8. Registered User
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. Registered User
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
•