MySQL RPAD() Function

  1. Overview
  2. MYSQL
  3. String Functions
  4. MySQL RPAD() Function

The RPAD() function in MySQL is used to right-pad a string with another string to make the total length of the resulting string equal to a specified length.

Syntax

RPAD(string, length, rpad_string)

Parameters

Parameter Description
string Required. The original string to be padded. If the original string is longer than the specified length, it will be truncated.
length Required. The total length of the resulting string after padding. If this value is smaller than the length of the original string, the string will be truncated.
rpad_string Required. The string that will be used for padding. It will be repeated to achieve the desired total length.

Definition and Usage

  • The RPAD() function pads the original string with the specified padding string from the right side to make the string reach the desired length.
  • If the length is smaller than the original string's length, the original string is truncated to fit the specified length.
  • If the padding string is smaller than the required space, it will be repeated as many times as needed.

Usage Examples

Example 1: Right-padding a string with a specific value

Right-pad the string "SQL Tutorial" with "ABC" to a total length of 20:

SELECT RPAD("SQL Tutorial", 20, "ABC");

Output:

RPAD("SQL Tutorial", 20, "ABC")
SQL TutorialABCABCABC

Explanation: The original string "SQL Tutorial" is right-padded with "ABC" to a total length of 20. The padding string "ABC" is repeated until the length reaches 20.

Example 2: Right-padding a CustomerName column with a specific value

Right-pad the text in the CustomerName column with "ABC", to a total length of 30:

SELECT RPAD(CustomerName, 30, "ABC") AS RightPadCustomerName
FROM Customers;

Output (example results):

RightPadCustomerName
JohnDoeABCABCABC
AliceSmithABCABC
BobJohnsonABCABC

Explanation: Each CustomerName is right-padded with "ABC" to a total length of 30 characters.

Technical Details

  • Works In: From MySQL 4.0 onwards.
  • Return Type: Returns a string with the specified total length, padded with the provided string from the right side.
  • Behavior:
    • If the specified length is shorter than the original string, the string is truncated.
    • If the padding string is shorter than the remaining space, it will repeat to fill the required length.
    • If the length of the original string is equal to or greater than the specified length, no padding occurs, and the original string is returned.

Applications

  1. Formatting Output: This function can be useful when you need to ensure that all strings in a report or display are the same length.
  2. Filling Fixed-Length Columns: When storing or presenting data that requires fixed-length fields, such as in files with specific formatting requirements (e.g., text files, logs).
  3. Uniform Display: Ensures consistency in text output when combining multiple pieces of information, such as displaying names and addresses in a standardized format.

Example Use Case

If you need to right-pad a product code to a fixed length of 10 characters for display purposes:

SELECT RPAD(ProductCode, 10, "0") AS PaddedProductCode
FROM Products;

This will ensure all product codes are displayed as 10 characters long, with 0 as the padding string.

Key Notes

  • The RPAD() function is the opposite of the LPAD() function, which left-pads a string.
  • It is particularly helpful in formatting strings when working with databases or reports that require standardized string lengths.
Was this article helpful?