Remove trailing zeros after decimal point in DB2

Question

I have the following values in label table in DB2 (version : 9.5.8)

select field4 from label with ur

1.5000
0.006
9.0001
104.2500
17.0000
3.5000

Is it possible to eliminate the trailing zeros after the decimal point with an update query in DB2?
If all the digits are zeros after the decimal point, I need to keep that as .0, however.

Expected output:

1.5
0.006
9.0001
104.25
17.0
3.5

Answer 1

You should probably consider changing the column to some kind of numeric. Anyhow, heres an idea:

with t(s) as (
    values '1.5000', '0.006', '9.0001','104.2500','17.0000','3.5'
) 
select translate(rtrim(translate(s,' ','0')),'0',' ') 
    || case when translate(rtrim(translate(s,' ','0')),'0',' ') like '%.' 
       then '0' 
       else '' 
       end 
from t

1.5
0.006
9.0001
104.25
17.0
3.5

Using a CTE for translate(rtrim(translate(s,' ','0')),'0',' ') will minimize the number of function calls.

with t(s) as (values '1.5000', '0.006', '9.0001','104.2500','17.0000','3.5')
   , u(s) as ( 
        select translate(rtrim(translate(s,' ','0')),'0',' ') 
        from t 
     ) 
select s || case when s like '%.' then '0' else '' end 
from u

1.5
0.006
9.0001
104.25
17.0
3.5

Answer 2

This is really hacky, but it should work for you.

SELECT
    trimmed || CASE WHEN LOCATE('.',trimmed) = LENGTH(trimmed) THEN '0' ELSE '' END
FROM (
    SELECT TRIM(TRAILING '0' FROM field4) AS trimmed
    FROM lablel
) A

The CASE statement re-adds a trailing zero if a decimal is the last character. If your string contains other decimals for whatever reason, this would break. If you were on DB2 9.7 (I'm assuming LUW here), then a better option would be to use LOCATE_IN_STRING, which can work backwards from the end of the string when you provide a negative start:

SELECT
    trimmed || CASE WHEN LOCATE_IN_STRING(trimmed,'.',-1) = LENGTH(trimmed) THEN '0' ELSE '' END
FROM (
    SELECT TRIM(TRAILING '0' FROM field4) AS trimmed
    FROM lablel
) A