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.