MySQL RPAD() Function

Summary: in this tutorial, you will learn how to use the MySQL RPAD() function to right-pad a string with a specific set of characters to a specified length.

Introduction to the MySQL RPAD function

The RPAD() function allows you to right-pad a string with some characters to a specified length. Here’s the syntax of the RPAD() function:

RPAD(string, length, pad_string)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• string: The input string that you want to right-pad.
• length: The total length of the resulting string after padding.
• pad_string: The character or string that you use for padding.

The RPAD() function returns the string, right-padded with the string pad_string to a length of length characters.

If the string is longer than the length, the RPAD() function shortens the string to the length character.

If any argument string, length, or pad_string is NULL, the RPAD() function returns NULL.

Note that RPAD() works with multibyte character encodings such as UTF-8 or UTF-16.

The RPAD function is useful when you need to format strings with a consistent length, align text in columns, or prepare data for display.

MySQL RPAD function examples

Let’s take some examples of using the MySQL RPAD function.

1) Basic MySQL RPAD() function example

The following example uses the RPAD() function to right-pad the string ‘123’ with three characters ‘0’ to make it six characters long:

SELECT RPAD('123', 6, '0');Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| RPAD('123', 6, '0') |
+---------------------+
| 123000              |
+---------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Right-pad table data

We’ll use the products table from the sample database for the demonstration:

The following example uses the RPAD() function to right-pad the product names from the products table with the character ‘.’ to make them 50 characters long:

SELECT 
  RPAD(productName, 50, '.') 
FROM 
  products 
LIMIT 
  5;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------------------------------------------+
| RPAD(productName, 50, '...')                       |
+----------------------------------------------------+
| 1969 Harley Davidson Ultimate Chopper............. |
| 1952 Alpine Renault 1300.......................... |
| 1996 Moto Guzzi 1100i............................. |
| 2003 Harley-Davidson Eagle Drag Bike.............. |
| 1972 Alfa Romeo GTA............................... |
+----------------------------------------------------+Code language: SQL (Structured Query Language) (sql)

Summary

• Use MySQL RPAD() function to right-pad a string with a specified character to achieve a consistent length