MySQL LPAD() Function

Summary: in this tutorial, you will learn how to use the MySQL LPAD() function to left-pad a string with a specific set of characters to a specified length.

Introduction to the MySQL LPAD() function

The LPAD() function allows you to left-pad a string with a specific set of characters to a specified length.

Here’s the syntax of the LPAD() function:

LPAD(string, length, pad_string)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• string: The input string that you want to left-pad.
• length: The total length of the resulting string after padding.
• pad_string: The string that you use for padding.

If any argument is NULL, the LPAD() will return NULL.

In practice, you use the LPAD() function format strings with consistent length, align text in columns or prepare data for display.

MySQL LPAD() function example

Let’s take some examples of using the LPAD() function.

1) Using LPAD() function to left-pad a numeric string with zeros

The following example uses the LPAD() function to left-pad a numeric string with zero. The result string is always 5 character length:

SELECT 
  LPAD('123', 5, '0');Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+
| LPAD('123', 5, '0') |
+---------------------+
| 00123               |
+---------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The following example shows how to left-pad numeric strings with zeros:

SELECT 
   LPAD('123', 5, '0'),
   LPAD('1234', 5, '0'),
   LPAD('12345', 5, '0');Code language: SQL (Structured Query Language) (sql)

Output:

+---------------------+----------------------+-----------------------+
| LPAD('123', 5, '0') | LPAD('1234', 5, '0') | LPAD('12345', 5, '0') |
+---------------------+----------------------+-----------------------+
| 00123               | 01234                | 12345                 |
+---------------------+----------------------+-----------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Using LPAD() function to left-pad a string with spaces

The following example uses the LPAD() function to left-pad a string with two spaces:

SELECT LPAD('MySQL',7, ' ');Code language: SQL (Structured Query Language) (sql)

Output:

+----------------------+
| LPAD('MySQL',7, ' ') |
+----------------------+
|   MySQL              |
+----------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) Using LPAD() function with table data

We’ll use the employees table from the sample database for the demonstration:

The following query uses the LPAD() function to generate a tree-like structure of employees and their managers within an organization.

WITH RECURSIVE employee_paths AS
  ( SELECT employeeNumber,
           reportsTo managerNumber,
		   firstName,
           1 lvl
   FROM employees e
   WHERE reportsTo IS NULL
     UNION ALL
     SELECT e.employeeNumber,
            e.reportsTo,
			e.firstName,
            lvl+1
     FROM employees e
     INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo )
SELECT employeeNumber,
       managerNumber,
       lvl,
	   CONCAT_WS(' ',LPAD('',lvl, '-'), firstName) firstName
FROM employee_paths ep
ORDER BY lvl, firstName;Code language: SQL (Structured Query Language) (sql)

Output:

+----------------+---------------+------+---------------+
| employeeNumber | managerNumber | lvl  | firstName     |
+----------------+---------------+------+---------------+
|           1002 |          NULL |    1 | - Diane       |
|           1076 |          1002 |    2 | -- Jeff       |
|           1056 |          1002 |    2 | -- Mary       |
|           1143 |          1056 |    3 | --- Anthony   |
|           1102 |          1056 |    3 | --- Gerard    |
|           1621 |          1056 |    3 | --- Mami      |
|           1088 |          1056 |    3 | --- William   |
|           1611 |          1088 |    4 | ---- Andy     |
|           1504 |          1102 |    4 | ---- Barry    |
|           1286 |          1143 |    4 | ---- Foon Yue |
|           1323 |          1143 |    4 | ---- George   |
|           1370 |          1102 |    4 | ---- Gerard   |
|           1188 |          1143 |    4 | ---- Julie    |
|           1501 |          1102 |    4 | ---- Larry    |
|           1165 |          1143 |    4 | ---- Leslie   |
|           1166 |          1143 |    4 | ---- Leslie   |
|           1337 |          1102 |    4 | ---- Loui     |
|           1702 |          1102 |    4 | ---- Martin   |
|           1401 |          1102 |    4 | ---- Pamela   |
|           1612 |          1088 |    4 | ---- Peter    |
|           1216 |          1143 |    4 | ---- Steve    |
|           1619 |          1088 |    4 | ---- Tom      |
|           1625 |          1621 |    4 | ---- Yoshimi  |
+----------------+---------------+------+---------------+
23 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this example, we use the LPAD() function to left-pad the character - based on the level of the employee in the reporting structure.

Here’s how it works.

1. WITH RECURSIVE employee_paths AS: This clause starts the definition of a common table expression (CTE) named employee_paths. The RECURSIVE keyword indicates that this CTE will be used recursively.
2. ( SELECT employeeNumber, reportsTo managerNumber, firstName, 1 lvl FROM employees e WHERE reportsTo IS NULL: This is the initial (or base) part of the CTE. It selects employees whose reportsTo field is NULL, which represents the top-level management or employees who have no managers. It assigns a level (lvl) of 1 to these top-level employees.
3. UNION ALL: This is used to combine the results of the initial and subsequent recursive parts of the CTE.
4. SELECT e.employeeNumber, e.reportsTo, e.firstName, lvl+1 FROM employees e INNER JOIN employee_paths ep ON ep.employeeNumber = e.reportsTo: This is the recursive part of the CTE. It selects employees (e) and their attributes (employee number, manager’s number, first name) where the employee’s manager number (reportsTo) matches the employeeNumber of the records in the CTE (employee_paths). It also increments the level (lvl) by 1 for each iteration.
5. The SELECT statement following the CTE is used to retrieve the final results from the CTE.
6. SELECT employeeNumber, managerNumber, lvl, CONCAT_WS(' ',LPAD('',lvl, '-'), firstName) firstName FROM employee_paths ep: This part of the query retrieves the following columns:
   • employeeNumber: The employee’s unique identifier.
   • managerNumber: The unique identifier of the employee’s manager.
   • lvl: The level or depth of the employee within the organization hierarchy.
   • CONCAT_WS(' ',LPAD('',lvl, '-'), firstName) firstName: It concatenates the following values:
     • LPAD('',lvl, '-'): It left-pads an empty string with hyphens (based on the lvl value) to create an indentation that represents the employee’s position in the hierarchy.
     • firstName: The first name of the employee.
7. ORDER BY lvl, firstName: The final result set is ordered first by the level (lvl) to maintain the hierarchy order, and then by the first name (firstName) within each level.

For detailed information on the recursive CTE, please check out the MySQL Recursive CTE tutorial.

Summary

• Use the MySQL LPAD() function to left-pad a string with a specific set of characters to a specified length.