SQL Padding a String

Introduction

Today, I had lots of fun with string manipulation in SQL. The problem was that I had a TotalHours field with the format “37.50? for 37 and a half hours per week. This needed to be translated into a 4 character code “3750?. Initially I took the LEFT 2 characters and the RIGHT 2 characters and joined them into a new field. This worked fine until I noticed some lazy people only did 5 hours per week. This still displayed “5.00?. In any normal language, you would do a search in the string for the character and then create substrings around that position. This was achieved in SQL using SUBSTRING and CHARINDEX.

The next problem was padding the resultant string so it was always 4 numeric characters. This was no easy feat as SQL seems to be missing some of the useful Oracle Padding functionality. So here is my workaround using the mysteriously named STUFF function.

declare @hours int
declare @outputlength tinyint 
set @outputlength = 4
set @hours = 500
print stuff(replicate(’0', @outputlength), _
    @outputlength - len(@hours)+1, len(@hours), cast(@hours as varchar(10))
)

As you will see, this creates a character string of 4 characters in length filled with our padding “0? using the REPLICATE function. It then finds a starting point for the delete function by subtracting the length of our @HOURS input from the desired @OUTPUTLENGTH. Next we specify the length of our deletion, this is the length of the string we are adding. Finally we cast our @HOURS int to a varchar and stick it on the end.

Result

"0500?

I have had a few responses about other ways to do this. To save you from picking through the section below, listed below are the good ones.

Seanf gave us:

select hours,right('0000' + convert(hours * 100.0,sql_varchar),4) as other

As you can see, this is a much simpler solution than mine.

Ewout Stortenbeker gave us:

select right('0000' + replace(@input, '.', ''), 4)

You can't get much better than that. It even does the removal of the decimal place or whatever separator you have. So if I was going to do the same thing again, I'd use Ewouts solution. Thanks to everyone that commented.

For other hints and tips, visit www.chemlock.co.uk as well as my output on life (not in binary).