The SQL rpad() function has been widely implemented across many major RDBMSs, including MySQL, Oracle, PostgreSQL, and MariaDB, to name just a few. But when it comes to SQL Server, we have a problem. SQL Server doesn’t currently provide us with an rpad() function.

But that’s not to say we can’t apply right padding in SQL Server. SQL Server still provides us with enough tools to get the job done. With a bit of work, we can get a similar result to what we might be able to achieve with rpad(). It may not be as elegant as a simple rpad() function, but at least it’s an option.

Example

We can use the left() function in a way that provides us with padding on the right side of a string. Here’s an example:

SELECT left('1234' + '...', 7);

Result:

1234...

In this example, I’m padding the value with dots, but this could easily be spaces or any other character. To get the padded effect, I’m cutting the resulting string to a specified length of 7. But this could be any number. This number is the number of characters I want the resulting string to be.

Here’s what happens when I change it to 6:

SELECT left('1234' + '...', 6);

Result:

1234..

This result is padded with two dots instead of three.

Here’s an example that pads a string with a space:

SELECT left('Homer' + '      ', 6) + 'Simpson';

Result:

Homer Simpson

In this case I decided to concatenate the result with another string (SImpson) so that we could see the effect of the padding to the right of Homer.

We can see that I passed more than one space, but the 6 ensured that only one space was used. Here’s what happens when I increase that number:

SELECT left('Homer' + '      ', 8) + 'Simpson';

Result:

Homer   Simpson

The space has become larger.

But if we use just one space, then the resulting string won’t have any more padding than just that space, regardless of how large the number is:

SELECT left('Homer' + ' ', 8) + 'Simpson';

Result:

Homer Simpson

Example 2: Including replicate()

We can expand on the previous example by incorporating the replicate() function into the equation. This function allows us to specify the number of characters to use for the padded character. In other words, it saves us from typing it out n number of times. Instead, we simply pass the character to the replicate() function and specify how many times it should be used:

SELECT left('1234' + replicate('.', 6), 7);

Result:

1234...

Again, we still need to specify how many characters should be in the final result (in this case 7), but the replicate() function saves us from typing the fill character multiple times.

That said, it’s probably easier to type the same character multiple times than to use the replicate() function, which is why I’ve listed it as a separate option. And there’s nothing wrong with having more options.

Conclusion

While SQL Server doesn’t have a dedicated rpad() function like many other SQL databases, there are still ways to apply right padding in SQL Server. By using the left() function in conjunction with concatenation, we can often get the same result that we might get with the rpad() function.

However, it’s not a perfect solution, as it requires us to hard-code a fixed length that we want the string to be padded to. This may have the effect of cutting off longer strings if we’re not careful (especially if we’re querying a large table).

That said, I hope you find it useful despite its shortcomings.