Padding a string in SQL Server

1

July 15, 2021 by Kenneth Fisher

I’ve been working on converting a piece of DB2 code into T-SQL and one of the functions I had to replace was lpad. It’s a simple enough function that will pad a string on the left with another string. So changing 1234 to 00001234. It’s a common enough task when formatting strings. And both DB2 and Oracle provide both lpad and rpad functions. However, guess what? SQL Server doesn’t. So how do we handle that in T-SQL? It’s a pretty easy pattern.

lpad

DECLARE @MyPad varchar(9) = '1234';
SELECT RIGHT(REPLICATE('0',9)+@MyPad,9);

rpad

DECLARE @MyPad varchar(9) = '1234';
SELECT LEFT(@MyPad+REPLICATE('0',9),9);

Explanation:

  • Use REPLICATE to create a string of the character you want to pad with that is the size of the total string you want to end up with. I’m creating a 9 character string here so I want to replicate the character 9 times. If you are padding with spaces you can use the SPACE function but I prefer to use REPLICATE so that I’m always following the same pattern.
  • Add the string created by REPLICATE to the side of @MyPad that you want to pad. Left for lpad, right for rpad. That leaves you with either 0000000001234 or 1234000000000.
  • Use either LEFT or RIGHT to trim the result to the size you want. You’ll use LEFT for rpad and RIGHT for lpad. So opposites.
  • Depending on if you think @MyPad is going to have spaces before/after the values you want you might need to surround it with LTRIM(RTRIM()) or in later versions of SQL you can use TRIM.

One thought on “Padding a string in SQL Server

  1. […] Kenneth Fisher pads some work: […]

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,576 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: