Ask a Question related to IBM DB2, Design and Development.
-
sujit #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
-
Select from function for part of column results
Hi Oisin On 03/02/05, Oisin Glynn (me@oisinglynn.com) wrote: This works great for a function returning a single column. I'm not sure how to do... -
ASP IN EXCEL, trailing zeros being truncated
Hi, I have an asp file which opens in Microsoft Excel. I have used "FormatNumber" function to force variables I display to be a certain number... -
missing strip function in DB2 8.1 -- remove leading zeros
sujit <yhkumars@yahoo.com> wrote: Where did you have the STRIP function? It didn't exist in version 7.... -
Using Replace function to remove two different characters
I'm using replace function to strip 0s from a particular string: <%=replace(rs1("racelength"),"0","''"%> but I need to also strip empty spaces... -
Result precision and scale for decimal arithmetic expression and aggregate function
Lan Huang wrote in message <48e0b0fb.0306270915.2a98365c@posting.google.com>... It depends. The easiest way to tell is to run your query... -
Tokunaga T. #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
-
Unregistered #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.
Unregistered Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
