Padding a string in SQL Server3
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.
DECLARE @MyPad varchar(9) = '1234'; SELECT RIGHT(REPLICATE('0',9)+@MyPad,9);
DECLARE @MyPad varchar(9) = '1234'; SELECT LEFT(@MyPad+REPLICATE('0',9),9);
- 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.
Category: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL | Tags: microsoft sql server, T-SQL
3 thoughts on “Padding a string in SQL Server”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] Kenneth Fisher pads some work: […]
I find LEFT and RIGHT overly complicated. My solution is
LPAD : SELECT REPLICATE(‘ ‘,25 -LEN(‘MySixLetterString’))+’MySixLetterString’
RPAD : SELECT ‘MySixLetterString’+REPLICATE(‘ ‘,25 -LEN(‘MySixLetterString’))
That’s certainly another way to do it 🙂