The LPAD() function in MySQL is used to left-pad a string with another string, making the final string reach a specified length.
Syntax
LPAD(string, length, lpad_string)
Parameters
| Parameter | Description |
|---|---|
string |
Required. The original string that will be padded. |
length |
Required. The total length of the string after it has been padded. |
lpad_string |
Required. The string to pad the original string with. |
Definition and Use
- Purpose: The
LPAD()function adds characters to the left side of the original string to make it reach the specified total length.- If the original string is longer than the target length, the extra characters from the original string are truncated.
- If the padding string is shorter than required, it will repeat as many times as needed to reach the specified length.
Usage Examples
Example 1: Basic Left Padding
Left-pad the string "SQL Tutorial" with "ABC" to a total length of 20:
SELECT LPAD("SQL Tutorial", 20, "ABC");
Output:
| LPAD("SQL Tutorial", 20, "ABC") |
|---|
| ABCABCABCSQL Tutorial |
Explanation: The string "SQL Tutorial" is padded with "ABC" until it reaches a total length of 20.
Example 2: Left Padding with Customer Names
Left-pad the text in the CustomerName column with "ABC" to a total length of 30:
SELECT CustomerName, LPAD(CustomerName, 30, "ABC") AS LeftPadCustomerName
FROM Customers;
Output (example data):
| CustomerName | LeftPadCustomerName |
|---|---|
| John Smith | ABCABCABCJohn Smith |
| Alice Brown | ABCABCABCAlice Brown |
Explanation: The customer names are padded with "ABC" until their total length reaches 30 characters.
Technical Details
- Availability: Available from MySQL 4.0 onwards.
- Return Type: Returns a string that is left-padded to the specified length.
- String Truncation: If the original string is longer than the target length, the function removes characters from the right side.
- Padding String: The padding string is repeated as many times as necessary to reach the specified length.
Applications
- Data Formatting: Useful when formatting data such as IDs, codes, or other fields that need to have a fixed length, particularly when preparing output for reports or displaying aligned data.
- String Manipulation: Often used in text processing or user input formatting to ensure consistent string length for storage or comparison purposes.
- User Interface Design: Ensures uniformity of data representation, especially when padding numeric values or codes with leading zeros or other characters.
Related Functions
- RPAD(): Similar to
LPAD(), but pads the string from the right side. - CONCAT(): Used to concatenate strings together, but not for padding.
- TRIM(): Removes unwanted characters (spaces by default) from the beginning or end of a string, in contrast to padding.
Key Notes
- If the padding string is empty or
NULL, the function may not work as expected. - If the length parameter is smaller than the length of the original string,
LPAD()will truncate the string from the right to match the specified length.
The LPAD() function is a versatile tool for string manipulation, particularly when dealing with uniform string formatting or creating consistent output in MySQL databases.