Professional Web Applications Themes

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. #1

    Default 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. #2

    Default 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. #3

    Default 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
    Quote Originally Posted by Tokunaga T. View Post
    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

Similar Threads

  1. Helper function inside template column
    By Chris in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: November 25th, 08:51 AM
  2. ASP IN EXCEL, trailing zeros being truncated
    By BARBARA FLOYD in forum ASP Components
    Replies: 1
    Last Post: November 11th, 10:30 AM
  3. Replies: 3
    Last Post: July 18th, 06:29 PM
  4. Replies: 4
    Last Post: July 11th, 03:30 PM
  5. Replies: 1
    Last Post: June 30th, 09:42 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139