Click here to Skip to main content
13,147,591 members (34,150 online)
Click here to Skip to main content
Add your own
alternative version


12 bookmarked
Posted 7 Jun 2006

SQL Padding a String

, 12 Oct 2006
Rate this:
Please Sign up or sign in to vote.
How to zero pad a string in Microsoft SQL


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.



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 as well as my output on life (not in binary).


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Web Developer
United Kingdom United Kingdom
Have been working with computers since the early 80's when our junior school got a BBC Micro. Every since then I've been building, fixing, configuring, installing, networking, coding and designing with them. At present I mainly code web applications in C#/ASP.NET. I'm very interested in Design Patterns and try and use these generic principles in all new projects to create truly n-tier architectures. You can keep up on current affairs on

Ok so the pictures not of me, it's my little girl who makes coding everyday worth while. I just hope she becomes a lawyer not a developer.

You may also be interested in...

Comments and Discussions

QuestionHow About Pin
seanf12-Jun-06 5:23
memberseanf12-Jun-06 5:23 
AnswerRe: How About Pin
GWSyZyGy12-Jun-06 6:43
memberGWSyZyGy12-Jun-06 6:43 
GeneralRe: How About Pin
Cheml0ck12-Jun-06 11:58
memberCheml0ck12-Jun-06 11:58 
AnswerRe: How About Pin
Cheml0ck12-Jun-06 11:54
memberCheml0ck12-Jun-06 11:54 
GeneralRe: How About Pin
Ewout Stortenbeker12-Jun-06 21:42
memberEwout Stortenbeker12-Jun-06 21:42 
GeneralRe: How About Pin
Cheml0ck13-Jun-06 21:05
memberCheml0ck13-Jun-06 21:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.170915.1 | Last Updated 12 Oct 2006
Article Copyright 2006 by Cheml0ck
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid