Padding, Rounding, Truncating and Removing Trailing Zeroes
June 18, 2003
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.
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.