Padding, Rounding, Truncating and Removing Trailing Zeroes - Page 2

Removing Trailing Zeroes Or Decimal Point

If you have a need for removing trailing zeroes from a decimal field, and/or displaying decimal numbers that contain integer values without a decimal point then this example might help you. To demonstrate how to remove trailing zeroes from a decimal number we will need a table that contains some decimal numbers. Here is a table named DECIMAL_TABLE that will be used in my example.

DCOL
--------- 
123.4500
123.0000
321.4500
999.4000
87.0000
 
100.0000
123.4599

The first example uses the REPLACE and RTRIM functions to remove extra zeroes and to determine whether to display the decimal point.

select replace(rtrim(replace(replace(rtrim(replace(dcol,'0',' '))
        ,' ','0'),'.',' ')),' ','.')
    from decimal_table

Let's look a little closer at how this works. First, this command uses the inner most REPLACE function to change all the zeroes to spaces. Next, the RTRIM function is used to remove the trailing spaces from the string, or basically to remove all the trailing zeroes. Remember the zeroes where replaced with spaces. Next, it changes all the remaining spaces back to zeroes with a REPLACE function. Now all that is left to do is to remove the decimal point for integer values. The first step to accomplish this is to use a REPLACE function to change the decimal point to a space. Next, the trailing spaces are removed with the RTRIM function, basically removing the decimal point for integer values. Now the last REPLACE converts a space to a decimal point, which essentially puts the decimal point back for all non-integer values.

Now, as with the padding example, there are multiple methods that can be used.

Here is another way to perform the same thing. Just to let you know this is less efficient then the example above.

select case when dcol=floor(dcol) 
              -- deal with the integer value   
              then 
                cast(floor(dcol) as char)
              -- deal with the decimal value
              else
                -- deal with the integer portion and append the decimal point
                rtrim(cast(floor(dcol) as char)) + '.' + 
                -- remove the trailing zeroes
                reverse(rtrim(cast(cast(floor(reverse(cast(dcol as char(10))))
                as int) as char))) 
        end
    from decimal_table

This example first uses a CASE statement to determine if the DCOL column contains an integer. It does this by using the FLOOR statement to round the DCOL value down to the nearest integer and then compares it to DCOL. If the rounded DCOL value equals the original DCOL column value then the record contains an INTEGER value. When DCOL contains an integer value then the FLOOR and CAST functions are used to return an integer value without the decimal point. If DCOL contains a non-integer value then the FLOOR function is used to get the integer portion of the decimal value, and the REVERSE function associated with FLOOR and RTRIM functions are used to truncate the trailing zeroes from the origin DCOL decimal value.