In SQL, RPAD()is used to pad the right part of a string with a specified character. The function can be used on strings and numbers, although depending on the DBMS, numbers may have to be passed as a string before they can be padded.

DBMSs that have an RPAD() function include MySQL, MariaDB, PostgreSQL, and Oracle.

DBMSs that don’t have an RPAD() function include SQL Server and SQLite.

Example

Here’s an example to demonstrate the RPAD() function:

SELECT RPAD('Look Right', 40);

Result:

+------------------------------------------+
| RPAD('Look Right', 40)                   |
+------------------------------------------+
| Look Right                               |
+------------------------------------------+

Here, the right part of the string is padded with a space (the default padding character), and the resulting string is 40 characters long (because I specified 40 as the second argument).

Oracle works the same, but we need to use FROM DUAL when doing a query like this (without querying an actual table):

SELECT RPAD('Look Right', 40) 
FROM DUAL;

Result:

                       RPAD('LOOKRIGHT',40) 
___________________________________________ 
Look Right                                 

In the above examples, I used a lot of padding in order to make the padding apparent.

Here’s another example that may demonstrate the right padding more clearly:

SELECT CONCAT(RPAD('abc', 4), 'def');

Result:

abc def

In this case, I concatenated abc with def but abc had right padding applied with the RPAD() function.

Specify a Padding Character

The padding doesn’t necessarily need to be a space. We can optionally add a third argument to specify the character (or characters) to use in the padding.

SELECT RPAD('Rat', 4, 't');

Result:

Ratt

It can also be used to right pad numbers with zeros (or another digit):

SELECT RPAD('7', 3, '0');

Result:

700

This obviously changes the number and in this case, the same could have been achieved simply by multiplying the number by 100. However, multiplying a number doesn’t actually apply padding to it.

The following example demonstrates what I mean:

SELECT RPAD('77', 3, '0');

Result:

770

Multiplying 77 by 100 would have had the wrong outcome.

In the above examples I passed the number as a string.

In some DBMSs (such as MariaDB and MySQL) we can pass the number as a number, as well as the number to pad it with:

SELECT RPAD(7, 3, 0);

Result:

700

We can also do this in Oracle:

SELECT RPAD(7, 3, 0) 
FROM DUAL;

Result:

700

But PostgreSQL has a problem with this:

SELECT RPAD(7, 3, 0);

Result:

ERROR:  function rpad(integer, integer, integer) does not exist

SQL Server

SQL Server doesn’t have an RPAD() function, but that doesn’t stop us from adding padding to numbers and strings.

Numbers

If the number has a fractional part, the FORMAT() function will suffice:

SELECT 
    FORMAT(0.7, '.000') AS "1",
    FORMAT(0.7, '0.00') AS "2",
    FORMAT(7.0, '0.00') AS "3";

Result:

+------+------+------+
| 1    | 2    | 3    |
|------+------+------|
| .700 | 0.70 | 7.00 |
+------+------+------+

The way it works is that we pass the number, followed by a format string. In the above example, the format string consists of custom numeric format specifiers that results in the original number having digits added to its fractional part. If there aren’t already that many digits in the original number, then it’s padded with zeros.

But if the number doesn’t have a fractional part, then we can use the REPLACE() function to remove the decimal separator that’s applied with the FORMAT() function:

SELECT 
    REPLACE(FORMAT(7, '.00', 'en-US'), '.', '') AS "1",
    REPLACE(FORMAT(17, '.00', 'en-US'), '.', '') AS "2",
    REPLACE(FORMAT(73.5, '.00', 'en-US'), '.', '') AS "3";

Result:

+-----+------+------+
| 1   | 2    | 3    |
|-----+------+------|
| 700 | 1700 | 7350 |
+-----+------+------+

This method can also be used to add leading zeros if required. Simply add them to the left of the decimal point in the format string.

Here, I explicitly used en-US as the (optional) third argument to ensure that the decimal separator is a period/full stop, which is the convention used by the en-US locale.

Text

Here’s a technique that can be used on textual data:

SELECT 
    LEFT('Dog' + '.........', 9) AS "1",
    LEFT('Horse' + '.........', 9) AS "2",
    LEFT('Crocodile' + '.........', 9) AS "3";

Result:

+-----------+-----------+-----------+
| 1         | 2         | 3         |
|-----------+-----------+-----------|
| Dog...... | Horse.... | Crocodile |
+-----------+-----------+-----------+

Although you’ll need to be careful not to accidentally chop off part of the string, or add unwanted spaces.