The LPAD function in MySQL is used to pad the left side of a string with a specified set of characters until the string reaches a desired length. This can be useful, for example, when you need to format data in a specific way, such as aligning numbers or creating fixed-width columns.

Syntax

Here is the syntax for the LPAD function:

LPAD(str, length, padstr)

str: The original string that you want to pad.
length: The desired length of the resulting string after padding.
padstr: The string that will be used to pad the original string. If this parameter is not specified, a space character will be used for padding.

Examples

Now, let’s look at an example to better understand how to use the LPAD function:

Suppose you have a table named employees with a column employee_name that contains employee names. You want to create a new column called padded_name where each name is left-padded with zeros to make it a total of 10 characters long. Here’s how you can achieve this using the LPAD function:

-- Create a new column padded_name
ALTER TABLE employees
ADD COLUMN padded_name VARCHAR(10);

-- Update the padded_name column using LPAD
UPDATE employees
SET padded_name = LPAD(employee_name, 10, '0');

In this example, the padded_name column is created, and the UPDATE statement is used to populate it with values from the employee_name column after applying the LPAD function. Each name is left-padded with zeros until it reaches a length of 10 characters.

Let’s illustrate with a sample data:

+--------------+-------------+
| employee_name| padded_name |
+--------------+-------------+
| John         | 0000000John |
| Alice        | 000000Alice |
| Bob          | 00000000Bob |
+--------------+-------------+

In the result, you can see that the padded_name column has been successfully created and populated using the LPAD function to left-pad the original names.

Here are some other examples of how the LPAD function can be used:

SELECT LPAD('Hello', 10, 'World');

This query will return the following result:

WorldHello

This is because the LPAD() function has been used to pad the string ‘Hello’ with the string ‘World’ to a length of 10 characters. The ‘World’ is padded to the left of the ‘Hello’ until the total length is 10 characters.

To pad numbers with leading zeros:

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

This will return the following result:

00123

The MySQL LPAD function is a useful tool for formatting strings and aligning data. It can be used to create consistent-looking data in reports or to prepare data for display in fixed-width formats.