In MariaDB, RPAD() is a built-in string function that pads the right part of a string with a certain number of a specified character.
For example, you can use RPAD() to pad the right part of a string with dots.
Syntax
The syntax goes like this:
RPAD(str, len [, padstr])Where str is the string to pad, and len is the number of characters to pad the string to. Note that this is not the amount of padding itself, rather it’s the total number of characters that the returned string will have after it’s been padded.
padstr is an optional argument that specifies which character to use for the padding.
Example
Here’s a basic example:
SELECT
RPAD('Fire', 8) AS "1",
RPAD('Fire', 15) AS "2",
'Fire' AS "3";Result:
+----------+-----------------+------+ | 1 | 2 | 3 | +----------+-----------------+------+ | Fire | Fire | Fire | +----------+-----------------+------+
The space character is the default character. Therefore, if you don’t include a third argument, a space is used. In this case, we didn’t specify a third argument, and so a space was used.
This example returns three columns. I did that in order to make it easier for you to see the effect of RPAD() more clearly. The padding has the effect of pushing the width of the column out to the specified padding length.
In this case, the first two columns use two different padding lengths, and the third one is the original (unpadded) string.
Specify a Padding Character
Here’s an example that uses a third argument. This specifies what character to use for the padding:
SELECT RPAD('Fire', 15, '.');Result:
+-----------------------+
| RPAD('Fire', 15, '.') |
+-----------------------+
| Fire........... |
+-----------------------+
The dots enable us to see the padding more clearly.
Padding Smaller than the Original String
If the second argument is less than the original string, then no padding is added, and the original string is shortened to the number of characters specified:
SELECT RPAD('Enlightenment', 7);Result:
+--------------------------+
| RPAD('Enlightenment', 7) |
+--------------------------+
| Enlight |
+--------------------------+
Database Example
Here’s an example of padding the right part of the values in a database column:
SELECT
RPAD(PetName, 15, '.') AS "Padded Name",
PetName AS "Original Name"
FROM Pets;Result:
+-----------------+---------------+ | Padded Name | Original Name | +-----------------+---------------+ | Fluffy......... | Fluffy | | Fetch.......... | Fetch | | Scratch........ | Scratch | | Wag............ | Wag | | Tweet.......... | Tweet | | Fluffy......... | Fluffy | | Bark........... | Bark | | Meow........... | Meow | +-----------------+---------------+
Null Arguments
If any (or all) of the arguments are null, the RPAD() function returns null:
SELECT
RPAD(null, 10, '.'),
RPAD('Coffee', null, '.'),
RPAD('Coffee', 10, null);Result:
+---------------------+---------------------------+--------------------------+
| RPAD(null, 10, '.') | RPAD('Coffee', null, '.') | RPAD('Coffee', 10, null) |
+---------------------+---------------------------+--------------------------+
| NULL | NULL | NULL |
+---------------------+---------------------------+--------------------------+
Oracle Mode
When not running in Oracle mode, if the result is empty (i.e. it has a length of zero) the result is an empty string.
However, when running in Oracle mode, the result is null.
Here it is in default mode (i.e. not in Oracle mode):
SELECT RPAD('', 0);Result:
+-------------+
| RPAD('', 0) |
+-------------+
| |
+-------------+
Now let’s switch to Oracle mode:
SET SQL_MODE=ORACLE;And run the code again:
SELECT RPAD('', 0);Result:
+-------------+
| RPAD('', 0) |
+-------------+
| NULL |
+-------------+
Missing Arguments
Calling RPAD() without at least two arguments results in an error:
SELECT RPAD('Coffee');Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'RPAD'
The same thing happens when calling RPAD() without any arguments:
SELECT RPAD();Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'RPAD'