LPAD() Function in MySQL

LPAD() function in MySQL is used to pad or add a string to the left side of the original string. 

Syntax :

LPAD(str, len, padstr)

Parameter : This function accepts three parameter as mentioned above and described below – 

• str – 
  The actual string which is to be padded. If the length of the original string is larger than the len parameter, this function removes the overfloating characters from string. 
• len – 
  This is the length of a final string after the left padding. 
• padstr – 
  String that to be added to the left side of the Original Str. 

Returns : It returns a new string of length len after padding. 

Example-1 : Applying LPAD() Function to a string to get a new padded string. 

SELECT LPAD("geeksforgeeks", 20, "*") AS LeftPaddedString;

Output :

LeftPaddedString
*******geeksforgeeks

Example-2 : Applying LPAD() Function to a string when the original string is larger than the len parameter. 

SELECT LPAD("geeksforgeeks", 10, "*") AS LeftPaddedString;

Output :

LeftPaddedString
geeksforge

Example-3 : LPAD Function can also be used to add a string for column data. To demonstrate create a table named Student. 

CREATE TABLE Student
(
Student_id INT AUTO_INCREMENT,  
Student_name VARCHAR(100) NOT NULL,
Student_Class VARCHAR(20) NOT NULL,
PRIMARY KEY(Student_id )
);

Now inserting some data to the Student table :

INSERT INTO Student
(Student_name, Student_Class)
VALUES
('Ananya Majumdar', 'IX'),
('Anushka Samanta', 'X'),
('Aniket Sharma', 'XI'),
('Anik Das', 'X'),
('Riya Jain', 'IX'),
('Tapan Samanta', 'X');

So, the Student Table is as follows. 

Student_id	Student_name	Student_Class
1	Ananya Majumdar	IX
2	Anushka Samanta	X
3	Aniket Sharma	XI
4	Anik Das	X
5	Riya Jain	IX
6	Tapan Samanta	X

Now, we are going to add some string to every string presented in the Student_Class column. 

SELECT Student_id, Student_name,
LPAD(Student_Class, 10, ' _') AS LeftPaddedString
FROM Student;

Output :

Student_id	Student_name	LeftPaddedString
1	Ananya Majumdar	_ _ _ _IX
2	Anushka Samanta	_ _ _ _ X
3	Aniket Sharma	_ _ _ _XI
4	Anik Das	_ _ _ _X
5	Riya Jain	_ _ _ _IX
6	Tapan Samanta	_ _ _ _ X

My Personal Notes arrow_drop_up

Save