MySQL LPAD() Function

Quiz

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

Print Page