MySQL - LPAD() Function

The MySQL LPAD() function is used to add padding to the left side of the string until it reaches the desired length.

Syntax

Following is the syntax of MySQL LPAD() function −

LPAD(str,len,padstr)

Parameters

Following are the parameters accepted by this function −

  • str is the original string to which you need to add the padding.

  • len is the desired length of the string up to which you need to add the padding.

  • padstr is the string you need to use as padding.

Return Value

This function returns a new string padded on the left with the specified padding string to reach the specified length.

Example

In the following example, we are using the LPAD() function to add dollar signs ('$') as padding to the left of the string 'Tutorialspoint' until it reaches a length of 25 −

SELECT LPAD('Tutorialspoint', 25,'$');

Following is the output of the above code −

LPAD('Tutorialspoint', 25,'$')
$$$$$$$$$$$Tutorialspoint

Example

You can also use LPAD() to add padding to numerical values, as shown in the following example −

SELECT LPAD(22556, 30,'%%@');

The output obtained is as follows −

LPAD(22556, 30,'%%@')
%%@%%@%%@%%@%%@%%@%%@%%@%22556

Example

The padding string can also be a number as shown below −

SELECT LPAD('Tutorialspoint', 30, 0);

We get the output as follows −

LPAD('Tutorialspoint', 30, 0)
0000000000000000Tutorialspoint

Example

If the original string is longer than the given length, it is truncated to match the desired length −

SELECT LPAD('Tutorialspoint', 5, '*' );

Following is the output of the above code −

LPAD('Tutorialspoint', 5, '*' )
Tutor

Example

If any of the arguments passed to this function is NULL, it returns NULL −

SELECT LPAD('Tutorialspoint', 25, NULL);

The result produced is as shown below −

LPAD('Tutorialspoint', 25, NULL)
NULL

Example

You can also use this function to add padding on the left side of the values of a column in a table.

Let us create a table named "PLAYERS" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE PLAYERS(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO PLAYERS VALUES
(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India'),
(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'),
(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka'),
(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India'),
(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India'),
(6, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

The PLAYERS table obtained is as follows −

ID First_Name Last_Name Date_Of_Birth Place_Of_Birth Country
1 Shikhar Dhawan 1981-12-05 Delhi India
2 Jonathan Trott 1981-04-22 CapeTown SouthAfrica
3 Kumara Sangakkara 1977-10-27 Matale Srilanka
4 Virat Kohli 1988-11-05 Delhi India
5 Rohit Sharma 1987-04-30 Nagpur India
6 James Anderson 1982-06-30 Burnley England

Following query add the string **---** as padding to the entities in the column FIRST_NAME and maintains a consistent length of 25 characters −

SELECT ID, First_Name, Last_Name, Country, LPAD(First_Name, 25, '**---**') as RESULT 
FROM PLAYERS;

After executing the above code, we get the following output −

ID First_Name Last_Name Country RESULT
1 Shikhar Dhawan India **---****---****--Shikhar
2 Jonathan Trott SouthAfrica **---****---****-Jonathan
3 Kumara Sangakkara Srilanka **---****---****---Kumara
4 Virat Kohli India **---****---****---*Virat
5 Rohit Sharma India **---****---****---*Rohit
6 James Anderson England **---****---****---*James
mysql-lpad-function.htm
Advertisements