Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 18, 2003

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

By Gregory A. Larsen

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.


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   
                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))) 
    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.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM