Skip to main content
Documents
Share
LinkedIn
Facebook
Twitter
Copy
ClausesStatementsKeywordsExpressionsFunctionsIndexesPerformance Optimization

MySQL LPAD() Function

The `LPAD()` function in MySQL is used to pad the left side of a string with a specified character until the string reaches a defined length. This function is useful for formatting output and ensuring consistent string lengths.

Usage

`LPAD()` is typically used when you need to align text in reports or format numbers with leading zeros. It takes three arguments: the original string, the desired length, and the padding character.

sql
LPAD(string, length, pad_string)

In this syntax, `string` is the original text, `length` is the total length of the result string, and `pad_string` is the character(s) used for padding.

Examples

1. Basic Padding

sql
SELECT LPAD('123', 5, '0');

This example pads the string `'123'` with zeros on the left, resulting in `'00123'`.

2. Padding with Characters

sql
SELECT LPAD('abc', 6, 'xy');

Here, the string `'abc'` is padded with the characters `'xy'` to a length of 6, producing `'xyxabc'`. If the `pad_string` is longer than needed, it will be truncated appropriately.

3. Padding in a Table Column

sql
SELECT LPAD(account_number, 10, '*') AS padded_account
FROM accounts;

This example pads the `account_number` column with asterisks to ensure each number is 10 characters long, aiding in consistent report formatting.

Tips and Best Practices

  • Use consistent padding characters. Choose a padding character that aligns with your formatting needs (e.g., zeros for numbers).
  • Ensure desired length is greater than the original string. The `LPAD()` function will return the original string if the specified length is less than or equal to the string's length.
  • Be cautious with multi-character padding. When using multiple characters for padding, understand how they will repeat to fill the space.
  • Handle empty or null `pad_string`. If the `pad_string` is empty or null, `LPAD()` will return `NULL`.
  • Optimize for readability. Use `LPAD()` to enhance the readability of output, especially in reports and user interfaces.
  • Consider performance on large datasets. Padding operations can impact query performance, so use `LPAD()` judiciously with large datasets.