Padding, Rounding, Truncating and Removing Trailing Zeroes

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.

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles