How the RPAD() Function Works in MySQL

In MySQL, the RPAD() function allows you to pad the right part of a string with one or more characters.

The way it works is, you specify the string to pad, the length of the padding, as well as the string of characters to use for the padding.

Syntax

The syntax goes like this:

RPAD(str,len,padstr)

Where str is the string to pad, len is the desired length of the string in characters after all padding has been applied, and padstr is the string to pad it with.

Example 1 – Basic Usage

Here’s an example of padding the right part of a string with an exclamation mark:

SELECT RPAD('Cat', 6, '!') AS Result;

Result:

+--------+
| Result |
+--------+
| Cat!!! |
+--------+

In this example the second argument is 6, which means that the whole string must end up being 6 characters in length after all padding has been applied. The third argument specifies which character to use for the padding.

So in this case, if we only want one exclamation mark, we can do this:

SELECT RPAD('Cat', 4, '!') AS Result;

Result:

+--------+
| Result |
+--------+
| Cat!   |
+--------+

Example 2 – Multiple Characters

You aren’t limited to just a single character. You can pad a string with any number of characters.

For example, we could take the previous example and add a space before the exclamation mark:

SELECT RPAD('Cat', 5, ' !') AS Result;

Result:

+--------+
| Result |
+--------+
| Cat !  |
+--------+

Note that we also increased the character count to 5 in order to accommodate the extra character.

Here’s another example using different characters:

SELECT RPAD('Dog', 7, 'gone') AS Result;

Result:

+---------+
| Result  |
+---------+
| Doggone |
+---------+

Example 3 – Second Argument is Too Small

If the value of the second argument is too small, you might end up with no padding:

SELECT RPAD('Cat', 3, '!') AS Result;

Result:

+--------+
| Result |
+--------+
| Cat    |
+--------+

In other cases, you could end up with the padding string being cut short, or could even cut the original string short:

SELECT 
  RPAD('Dog', 6, 'gone'),
  RPAD('Dog', 2, 'gone');

Result:

+------------------------+------------------------+
| RPAD('Dog', 6, 'gone') | RPAD('Dog', 2, 'gone') |
+------------------------+------------------------+
| Doggon                 | Do                     |
+------------------------+------------------------+

Note that these examples are for demonstration purposes only. In most cases, you wouldn’t use RPAD() to simply join two words together. To do that, you’d be better off using CONCAT() instead.

Example 4 – A Database Example

Here’s an example of selecting data from a database, and padding it on its right side:

SELECT ArtistName, RPAD(ArtistName, 20, '.')
FROM Artists;

Result:

+------------------------+---------------------------+
| ArtistName             | RPAD(ArtistName, 20, '.') |
+------------------------+---------------------------+
| Iron Maiden            | Iron Maiden.........      |
| AC/DC                  | AC/DC...............      |
| Allan Holdsworth       | Allan Holdsworth....      |
| Buddy Rich             | Buddy Rich..........      |
| Devin Townsend         | Devin Townsend......      |
| Jim Reeves             | Jim Reeves..........      |
| Tom Jones              | Tom Jones...........      |
| Maroon 5               | Maroon 5............      |
| The Script             | The Script..........      |
| Lit                    | Lit.................      |
| Black Sabbath          | Black Sabbath.......      |
| Michael Learns to Rock | Michael Learns to Ro      |
| Carabao                | Carabao.............      |
| Karnivool              | Karnivool...........      |
| Birds of Tokyo         | Birds of Tokyo......      |
| Bodyjar                | Bodyjar.............      |
+------------------------+---------------------------+