How to Use the LPAD() Function in MySQL

08/06/2021

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.

#MySQL