How to Use the LPAD() Function in MySQL

Contents
In this article, you will learn how to use the LPAD() function in MySQL.
Using the LPAD() function in MySQL
In MySQL, the LPAD() function is used to pad a string with a specified character or characters on the left until it reaches a specified length.
Syntax
LPAD(str, length, pad_string);
Examples
Here are some examples of using the LPAD() function in MySQL:
Padding a string with zeros
To pad a string with zeros on the left until it reaches a specified length, you can use the LPAD() function.
Syntax
SELECT LPAD(str, length, '0');
Example
SELECT LPAD('123', 5, '0');
Output:
+---------------+
| LPAD('123', 5, '0')|
+---------------+
| 00123 |
+---------------+
In this example, the LPAD() function pads the string ‘123’ with zeros on the left until it reaches a length of 5.
Padding a string with spaces
You can also pad a string with spaces on the left until it reaches a specified length using the LPAD() function.
Syntax
SELECT LPAD(str, length, ' ');
Example
SELECT LPAD('hello', 10, ' ');
Output:
+------------------+
| LPAD('hello', 10, ' ')|
+------------------+
| hello |
+------------------+
In this example, the LPAD() function pads the string ‘hello’ with spaces on the left until it reaches a length of 10.
Padding a column value
You can also use the LPAD() function to pad column values with a specified character or characters.
Syntax
SELECT LPAD(column_name, length, pad_string) FROM table_name;
Example
SELECT LPAD(price, 8, '*') FROM products;
Output:
+------------------+
| LPAD(price, 8, '*')|
+------------------+
| ****19.99 |
| **499.99 |
| **199.99 |
+------------------+
In this example, the LPAD() function pads the price column in the products table with asterisks on the left until it reaches a length of 8.