Padding, Rounding, Truncating and Removing Trailing Zeroes

Wednesday Jun 18th 2003 by Gregory A. Larsen
Share:

Greg Larsen offers some tips for displaying numeric fields as strings. Included are examples on how to pad zeroes on the left side so that numbers will be the same length, how to pad the left side of a number with spaces so that each number is right justified, how to display a decimal column without any trailing zeroes or decimal point and how to round a decimal number to a specific number of decimal places.

In this article, I am going to show you a number of tips for displaying numeric fields as strings. I will show you how to pad zeroes on the left side of numeric numbers, so that each number regardless of the size will be the same length. Also along those same lines below you will find out how to pad the left side of a number with spaces so that each number is right justified. Another tip will describe how to display a decimal column without any trailing zeroes or decimal point if the decimal value is an integer. I will also show you how to round a decimal number to a specific number of decimal places.

Padding Integers

You never know when you will have a need to take some integer data from a table and show the output where all numbers are neatly lined up, regardless of the length. If the integer numbers are of different lengths then you will need to either pad integers with spaces, so they are all right justified in your display, or pad with zeroes to fill out the display. I will show you how to both pad with space and zeroes.

For each of these examples I will need a table containing integers. I will use the job_lvl TINYINT column of the employee table in the pubs database for each example. Here is the value for the job_lvl in the first ten rows of the employee table.

job_lvl 
------- 
35
89
140
35
120
227
215
87
200
100

As you can see, we have two or three character values for the job_lvl column. Let's say your application would like to right justify all those two digit numbers, so the right most digit for all values (two and three digit numbers) are aligned. To right justify we will need to add a single space in front of all two-digit numbers.

Here is one method of padding a single space to the job_lvl column using the STR function:

select top 10 str(job_lvl,3,0) as jl from employee

The STR function is used to convert numeric data to character data. The STR function accepts three parameters, a numeric field, the length of string to create, and the number of decimal places to return for non-integer data types. In my example, the numeric field is the TINYINT job_lvl field from the employee table. The length I wanted to return is 3, and the number of decimal places is 0 (zero). Just to let you know, the third parameter is optional, so this particular example could have also been coded as:

select top 10 str(job_lvl,3) as jl from employee

The output from both of these commands would look like this:

jl   
---- 
 35
 89
140
227
215
 87
200
100
 35
120

By just adding a REPLACE statement to the above example, we can pad zeroes instead of spaces.

select top 10 cast(replace(str(job_lvl,3),' ','0')as char(3)) as jl from employee

Here the REPLACE statement changes the space to a zero. The reason the CAST statement was needed was to produce a three-character display. Without the CAST function, Query Analyzer would display a column with as many characters allowed by the "maximum characters per column" setting. The output from this command would look like this:

jl   
---- 
035
089
140
035
120
227
215
087
200
100

Now understand, this is not the only method that can be used to pad numeric numbers with zeroes. Here are a number of other methods to accomplish the same thing.

select top 10 right('000' + convert(varchar(3),job_lvl), 3) as jl from employee  

select top 10 RIGHT(1000 + job_lvl,3) as jl from employee 

 
select top 10 right('000000' + rtrim(cast(job_lvl as char(3))), 3)as jl from employee

select top 10 cast(replicate('0', 3 - len(job_lvl)) + 
              cast(job_lvl as char(3)) as 
              char(3)) as jl from employee  

As I was putting these examples together, I ran a little test to determine which method might be the most efficient. This test consisted of processing each one of these commands 100,000 times. Which one do you think is most efficient? See the note at the bottom of this article to find out what my test revealed.

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.

Rounding Number to a Certain Number of Decimal Places

SQL Server provides the ROUND function to perform rounding operations. When rounding decimal numbers, there are three kinds of rounding that your applications might need, round up, round down, or true rounding. My definitions for these are as follows:

  • Rounding up means always rounding to the next highest number;

  • Rounding down means always rounding down to the next lowest number;

  • True rounding means rounding up to the next highest number if the part that is to be truncated is 5 or greater, and rounding down if the part that is to be truncated is less than 5. Round down is also known as truncating.

The ROUND function supports rounding down and true rounding, although it does not support my definition of rounding up. To round up you will need to perform some extra steps prior to using the ROUND function. I will build an example for each of these rounding methods. The ROUND function has the following syntax:

ROUND ( numeric_expression , length [ , function ] )

Where the numeric_expression is the number you want to round, length is the number of characters to leave while rounding to the right or left of the decimal point. If the length is positive, then numbers are rounded to the right of the decimal point, where as when the length is negative then numbers are rounded to the left of the decimal point. The function parameter is optional. This parameter determines whether the ROUND function will round or truncate the numeric_expression. If the function parameter is a zero (0), the default, then rounding will occur; any other value for this parameter will cause the ROUND function to truncate the numeric_expression.

Let's say you have a loan application. When you calculate the interest amount on a given loan, you usually end up with a fractional portion of a cent. However, you cannot charge a fraction of a cent, since there is no way for the customer to pay a fraction of a cent. Therefore, instead you decide your application should round the interest amount up, so you can collect a fraction of a cent more on each interest payment made. Here is some code that would do just that:

declare @loan_amt decimal(10,2) 
declare @interest_rate decimal(7,5)
declare @monthly_interest_amt decimal(10,4)
set @loan_amt = 123456.78
set @interest_rate = 6.75
select @monthly_interest_amt= (@loan_amt * (@interest_rate/100)) * 1/12
print @monthly_interest_amt
select @monthly_interest_amt = @monthly_interest_amt + .005
print @monthly_interest_amt
select  cast(round (@monthly_interest_amt,2,0) as decimal(10,2))

As you can see from this example, I calculated the monthly interest amount by using the following formula (@loan_amt * (@interest_rate/100)) * 1/12. This calculation produces a monthly interest amount of 694.4444. Now since the there is no way to force the ROUND function to round up, I added .005 (B= cent) to the calculated monthly interest amount. By doing this, the final displayed monthly interest amount is 694.45.

Now say you have a bank account type of application and you need to calculate the amount of interest a particular account receives each month. Like the previous example, you do not want to give an account a fraction of a cent, so your interest calculation needs to truncate the factional cent value down to the nearest penny. The ROUND function provides the round down function, by providing a positive number for the function parameter. Here is an example that shows how to round down, to truncate the fraction of a cent from the calculated interest earned for a particular bank account balance.

declare @bank_account_bal decimal(10,2) 
declare @interest_rate decimal(7,5)
declare @interest_earned decimal(10,4)
set @bank_account_bal = 2516.78
set @interest_rate = 4.99
select @interest_earned= (@bank_account_bal * (@interest_rate/100)) * 1/12
print @interest_earned
print @interest_earned
select round (@interest_earned,2,1) 
select cast(round (@interest_earned,2,1) as decimal(10,2))

For the last rounding example, I am going to show two different ways to do true rounding. One method uses the ROUND function and the other method uses the CAST function. Let's assume we have an application that calculates the average number of transactions per second for the day. This application calculates the average transaction per second, by taking the number of transactions for a day and divides by the number of seconds in a day. Below you will find code that does true rounding while calculating the average transaction per second for two different days.

set nocount on 
declare @num_of_trans decimal (15,0)
declare @avg_trans_per_sec decimal (15,8)
-- Round up example for day 1
print 'Example 1'
set @num_of_trans = 123456
set @avg_trans_per_sec = @num_of_trans / (24*60*60)
select @avg_trans_per_sec, 
       round(@avg_trans_per_sec,2,0),
       cast(round(@avg_trans_per_sec,2,0) as decimal (15,2)),
       cast(@avg_trans_per_sec as decimal (15,2))
-- Round down example for day 2
print 'Example 2'
set @num_of_trans = 123456789
set @avg_trans_per_sec = @num_of_trans / (24*60*60)
select @avg_trans_per_sec, 
       round(@avg_trans_per_sec,2),
       cast(round(@avg_trans_per_sec,2) as decimal (15,2)),
       cast(@avg_trans_per_sec as decimal (15,2))

The output from this code looks like this:

Example 1
                                                                        
----------------- ----------------- ----------------- ----------------- 
 
1.42888889        1.43000000        1.43              1.43

Example 2
                                                                        
----------------- ----------------- ----------------- ----------------- 
1428.89802083     1428.90000000     1428.90           1428.90

As you can see from the output of these two examples, the ROUND function and the CAST perform true rounding. In example 1, I set the function parameter to 0 for the call to the ROUND function, where as I took the default value for the function parameter in the second example. Note that even though you specify to round to two decimal places the round function does not remove the trailing zeroes. If you want to use the ROUND function as well as remove the trailing zeroes, you will also need to use the CAST statement to truncate the decimal number. Now using the CAST function can perform the rounding and truncation while casting the output, as the example demonstrates.

Padding with Zeroes Which One Performs Better

Ok, which one do you think performs better? From my testing, the following example performs 24% faster then the next closest one.

select top 10 right('000' + convert(varchar(3),job_lvl), 3) as jl from employee  

Just in case there are any questions about how I did my performance testing, here is my performance benchmark code. I ran the following code on a standalone machine. This code was run three times for each example and I recorded the duration for each time. I then threw out the fastest and slowest time, and associated the middle duration as the time it takes to perform each example 100,000 times.

declare @j int
declare @I int
declare @s datetime
set @I = 0 
set @s = getdate()
while @I < 100000
begin 
  select top 10 @j=right('000' + convert(varchar(3),job_lvl), 3) from employee
  set @I = @I + 1
end
print cast(datediff(ms,@s,getdate()) as char) 

Conclusion

Depending on your application requirements, you may need to remove trailing zeroes, add leading zeroes and/or round decimal numbers. I have shown you a number of techniques to do each of these. Also, remember there are many ways to perform a given task. If you know of more than one way to accomplish your task and performance is a consideration then run your code through a performance test like the one I did above to determine which method is more efficient.

» See All Articles by Columnist Gregory A. Larsen

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved