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
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
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
