# SQL function to remove trailing zeros from a decimal column - IBM DB2

The volume column is of type decimal(31,6). select distinct volume from i_item_master VOLUME --------------------------------- 0.100000 0.500000 1.005000 2.000000 4.000000 5.000000 How can I remove the trailing zeros ? If there are just zeros after the decimal point, then I don't want to show the decimal point also. I want the output to look like: VOLUME --------------------------------- 0.1 0.5 1.05 2 4 5 Would it be possible to do this without using a UDF ?...

1. ## SQL function to remove trailing zeros from a decimal column

The volume column is of type decimal(31,6).

select distinct volume from i_item_master

VOLUME
---------------------------------
0.100000
0.500000
1.005000
2.000000
4.000000
5.000000

How can I remove the trailing zeros ?
If there are just zeros after the decimal point,
then I don't want to show the decimal point also.

I want the output to look like:

VOLUME
---------------------------------
0.1
0.5
1.05
2
4
5

Would it be possible to do this without using a UDF ?
sujit Guest

2. ## Re: SQL function to remove trailing zeros from a decimal column

Example1:
----------------------------------------------------------------
SELECT Volume
, TRANSLATE(LTRIM(RTRIM(TRANSLATE(CHAR(Volume), ' ', '0'))), '0',
' ') AS Volume_edit1
FROM (SELECT DEC(Volume, 31, 6) Volume
FROM (VALUES
0.100000
, 0.500000
, 1.005000
, 2.000000
, 4.000000
, 5.000000
) AS X1(Volume)
) AS X2
!
---------------------------------------------------

VOLUME VOLUME_EDIT1
--------------------------------- ---------------------------------
0.100000 .1
0.500000 .5
1.005000 1.005
2.000000 2.
4.000000 4.
5.000000 5.

6 record(s) selected.

Example2:
----------------------------------------------------------------
SELECT Volume
, CASE WHEN ABS(Volume) < 1. THEN '0' ELSE '' END ||
TRANSLATE(RTRIM(TRANSLATE(TRANSLATE(LTRIM(RTRIM(TR ANSLATE(CHAR(Volume),
' ', '0'))), '0', ' '), ' ', '.')), '.', ' ') AS Volume_edit2
FROM (SELECT DEC(Volume, 31, 6) Volume
FROM (VALUES
0.100000
, 0.500000
, 1.005000
, 2.000000
, 4.000000
, 5.000000
) AS X1(Volume)
) AS X2
!
---------------------------------------------------

VOLUME VOLUME_EDIT2
--------------------------------- ----------------------------------
0.100000 0.1
0.500000 0.5
1.005000 1.005
2.000000 2
4.000000 4
5.000000 5

6 record(s) selected.
Tokunaga T. Guest

3. ## Re: SQL function to remove trailing zeros from a decimal column

SELECT Volume
, TRANSLATE(LTRIM(RTRIM(TRANSLATE(CHAR(Volume), ' ', '0'))), '0',
' ') AS Volume_edit1
FROM (SELECT DEC(Volume, 31, 6) Volume
FROM (VALUES
0.100000
, -0.500000
, -1.005000
, 2.000000
, 4.000000
, 5.000000
) AS X1(Volume)
) AS X2

for negative values it give wrong results
Originally Posted by Tokunaga T.
Example1:
----------------------------------------------------------------
SELECT Volume
, TRANSLATE(LTRIM(RTRIM(TRANSLATE(CHAR(Volume), ' ', '0'))), '0',
' ') AS Volume_edit1
FROM (SELECT DEC(Volume, 31, 6) Volume
FROM (VALUES
0.100000
, 0.500000
, 1.005000
, 2.000000
, 4.000000
, 5.000000
) AS X1(Volume)
) AS X2
!
---------------------------------------------------

VOLUME VOLUME_EDIT1
--------------------------------- ---------------------------------
0.100000 .1
0.500000 .5
1.005000 1.005
2.000000 2.
4.000000 4.
5.000000 5.

6 record(s) selected.

Example2:
----------------------------------------------------------------
SELECT Volume
, CASE WHEN ABS(Volume) < 1. THEN '0' ELSE '' END ||
TRANSLATE(RTRIM(TRANSLATE(TRANSLATE(LTRIM(RTRIM(TR ANSLATE(CHAR(Volume),
' ', '0'))), '0', ' '), ' ', '.')), '.', ' ') AS Volume_edit2
FROM (SELECT DEC(Volume, 31, 6) Volume
FROM (VALUES
0.100000
, 0.500000
, 1.005000
, 2.000000
, 4.000000
, 5.000000
) AS X1(Volume)
) AS X2
!
---------------------------------------------------

VOLUME VOLUME_EDIT2
--------------------------------- ----------------------------------
0.100000 0.1
0.500000 0.5
1.005000 1.005
2.000000 2
4.000000 4
5.000000 5

6 record(s) selected.
Unregistered Guest

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•