Removing Trailing Zeroes Or Decimal Point
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.
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'),'.',' ')),' ','.')
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.
as with the padding example, there are multiple methods that can be used.
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
cast(floor(dcol) as char)
-- deal with the decimal value
-- 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)))
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.