CODEWITHSUNDEEP
db2
solquest
solquest

Reputation: 77

DB2 remove trailing 0 and

I have BIGINT like

5500
10800
700
10450
10000

and on a select I need to divide them for 10000 and then I want to remove all ending 0 decimals. So desidered output should be

0.55
1.08
0.07
1.045
1

I've tried with

rtrim (rtrim (cast (cast ((cast (column_A as float) / 10000) as decimal(31,4)) as varchar(32000)), '0'), '.')

for removing at first the trailing '0' and then the trailing '.' But this is what I get

.55
1.08
.07
1.045
1

What should I do for keeping the first 0 for 0.55 and 0.07? I thought rtrim was removing them only from the right... I've tried also using

TRIM(TRAILING '.' FROM TRIM(TRAILING '0'

instead of RTRIM but nothing changes.

I'm on windows with DB2 v.10.5 and IBM Data Studio 4.1.2.

Upvotes: 0

Views: 234

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12339

cast (0.1 as varchar (x)) returns .1, not 0.1.
But cast (decfloat (0.1) as varchar (x)) returns 0.1.
So try the following:

cast (decfloat (i) / 10000 as varchar (20))

Upvotes: 1

Related Questions