Click here to Skip to main content
Click here to Skip to main content

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

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).

License

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

Share

About the Author

Cheml0ck
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 www.steelcurve.com


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.

Comments and Discussions

 
QuestionHow About Pinmemberseanf12-Jun-06 5:23 
AnswerRe: How About PinmemberGWSyZyGy12-Jun-06 6:43 
GeneralRe: How About PinmemberCheml0ck12-Jun-06 11:58 
AnswerRe: How About PinmemberCheml0ck12-Jun-06 11:54 
GeneralRe: How About PinmemberEwout Stortenbeker12-Jun-06 21:42 
GeneralRe: How About PinmemberCheml0ck13-Jun-06 21:05 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 12 Oct 2006
Article Copyright 2006 by Cheml0ck
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid