Skip to content

Padding values in SQL

August 22, 2012

I thought for this blog post I would do something kind of different.  I thought it might be fun to share with you a scenario or problem that I encountered recently.  Then I will detail out the solution that I came up with.

Todays ‘problem’ is a rather simple one but I think can frequently be useful so I decided to share it.

About a week or so ago I was received the following question from a friend:

I need to be able to output numbers from SQL in a fixed width format. For example:

134 as 0000000134
1024 as 0000001024

What is the quickest and easiest way to do that in SQL?

Now before I dive into the solution I came up with I want to say that normally this sort of string/number manipulation is best done in the actual application or UI.  It is generally more efficient to do it there.  However, there are times when that is not an option so doing it in SQL can still be useful.

Looking at the above question from my friend I can make a few assumptions.  First it looks like we have an field with a integer value stored in it and we need to return a string (or (n)varchar) value that pads the integer with a number of zeros on the left hand side.  Finally it looks as if our final string value needs to be 10 characters long.

So ideally what I would like to do is give my fried a segment of code that he can drop into his select statement (replacing field names and lengths as needed.  Now I know SQL does not have any build in string functions to do this kind of padding (another reason to normally do this as part of the application layer).

The only viable option we have is to use the right function (we technically could build a user defined function and use a loop to pad the value one character at a time but that is not very efficient).  The idea being to take our value (134) , add more 0’s than we need to the front of it (0000000000134) and then use the right function to trim it down to the length we need (0000000134).

Of course if we stop there we might end up with some code that looks like this:

select RIGHT(0000000000 + 134, 10)

Of course the above line of code will not work the way we want. It returns a value of 134 without any leading zeros.  This of course happens because SQL is treating the 0’s and the 134 as integers and not strings so it is simple adding the two together. What we need to do is force SQL to treat both of the values as strings.  That would look like this:

select RIGHT(‘0000000000’ + CAST(134 as varchar(10)), 10)

If we execute the above line we will now get the value we are looking for:

0000000134

There is one final thing that we need to account for.  What if the value we are trying to pad (remember in the scenario it is a field is coming from a database) was null?

select RIGHT(‘0000000000’ + CAST(null as varchar(10)), 10)

If you have ever tried to perform any string manipulation on a null value you will know that the result is always null.  What we need to do is wrap the value in an isNull function:

select RIGHT(‘0000000000’ + CAST(isNull(134, ”) as varchar(10)), 10)

The above statement should always return 10 characters no matter what data is passed into it.

Before we end this little exercise I want to show you how you can use the REPLICATE function along with the RIGHT function to simplify the above statement a bit.  The way the REPLICATE function works is it takes a value that you specify and repeats it a number of times that you define. So as an example:

select REPLICATE(‘0’, 10)

Would return:

0000000000

I am sure that you can see where we are goning with this.  You can insert the REPLICATE function into our right statement like this:

select RIGHT(REPLICATE(‘0’, 10) + CAST(isNull(134, ”) as varchar(10)), 10)

I find that this makes things a little more readable and a lot easier to maintain.

I hope you found the above useful. Let me know if you have any questions of comments.

Jeff

www.statusnotquo.com

Advertisements
3 Comments leave one →
  1. Lorraine permalink
    September 5, 2012 8:33 pm

    Nice job! I like this.

  2. February 13, 2013 12:24 am

    CxusqZ uilexgtrdwnd

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: