LPAD() Function in MySQL
Last Updated :
21 Jun, 2021
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 |
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...