T-SQL String Padding with Replicate

by Otto on October 19, 2007

I’ve been writing T-SQL since Microsoft SQL Server 6.5, however, I never had to use the REPLICATE function.  Heck, I didn’t even know the command existed until a couple of months ago.  Note, the REPLICATE function has nothing to do with SQL replication.

Since we are going through a conversion at work, we’re massaging a lot of data.  I was asked to help write a couple queries that pad fields with leading zeros.  For example, if an interger based column had the number 8 in it, we want that number to be 0008.  Likewise, if the field had the number 80, we want it to be 0080.  And so on…

My first thought was to write a quick and dirty c# application to deal with the data, but when you’re dealing with a large amount of data, the c# application will take too long to run.  Our goal is to automate the massaging and extracting of data, so keeping the solution native to SQL was necessary.

Let’s assume we have a table called PHONENUMBERS and a interger field called PHONESUFFIX.  The following query will pad the PHONESUFFIX field with leading zeros.

SELECT 
 PhonePrefix
 , CAST(REPLICATE(‘0’, 4 – LEN(CAST(PhoneSuffix AS varchar(4)))) + CAST(PhoneSuffix AS varchar(4)) AS varchar(4)) AS PhoneSuffix
FROM PhoneNumbers

All the magic happens in the following line:
  CAST(REPLICATE(‘0’, 4 – LEN(CAST(PhoneSuffix AS varchar(4)))) + CAST(PhoneSuffix AS varchar(4)) AS varchar(4)) AS PhoneSuffix

The first CAST will cast the entire field as a varchar(4), since we need our output to have 4 characters.  Then, the REPLICATE function will pad the field with 4 zeros minus the length of the current record.  Now that we have the leading zeros, all that is left is to concatinate our data with leading zeros.  SQL won’t automatically convert our integer field during a string concatination, so we have to cast it as a varchar(4).

You can use the command to pad something with trailing zeros as well.  Just flip 3rd CAST statement and REPLICATE command.

Pretty cool and best of all it is native to Microsoft SQL Server.

{ 2 comments }

Rob June 16, 2008 at 8:59 am

Thank you so much for this post!I never heard of REPLICATE either until now.Very cool and a huge time saver!Thanks again!

Jas January 13, 2009 at 3:33 am

Thanks! Never heard of REPLICATE… this is a time saver!

Comments on this entry are closed.

Previous post:

Next post: