How can we use LPAD() or RPAD() functions with the values in the column of a MySQL table?

For using LPAD() or RPAD() functions with the column values we need to specify the column name as the first argument of these functions. Following the example from ‘Student’ table will make it clearer −

Example

mysql> Select Name, LPAD(Name,10,'*') from student;

+---------+-------------------+
| Name    | LPAD(Name,10,'*') |
+---------+-------------------+
| Gaurav  | ****Gaurav        |
| Aarav   | *****Aarav        |
| Harshit | ***Harshit        |
| Gaurav  | ****Gaurav        |
| Yashraj | ***Yashraj        |
+---------+-------------------+
5 rows in set (0.08 sec)

mysql> Select Name, RPAD(Name,10,'*') from student;

+---------+-------------------+
| Name    | RPAD(Name,10,'*') |
+---------+-------------------+
| Gaurav  | Gaurav****        |
| Aarav   | Aarav*****        |
| Harshit | Harshit***        |
| Gaurav  | Gaurav****        |
| Yashraj | Yashraj***        |
+---------+-------------------+

5 rows in set (0.00 sec)

We can also use both the functions in one query for column’s value as follows −

mysql> Select Name, RPAD(LPAD(Name,10,'* '),14,'* ') from student;

+---------+----------------------------------+
| Name    | RPAD(LPAD(Name,10,'* '),14,'* ') |
+---------+----------------------------------+
| Gaurav  | * * Gaurav* *                    |
| Aarav   | * * *Aarav* *                    |
| Harshit | * *Harshit* *                    |
| Gaurav  | * * Gaurav* *                    |
| Yashraj | * *Yashraj* *                    |
+---------+----------------------------------+

5 rows in set (0.00 sec)

Ankith Reddy

Published on 20-Feb-2018 08:41:37

• Related Questions & Answers
• How can we use MySQL LPAD() and RPAD() functions in the same query for padding the string to both sides, left and right, of the original string?
• What MySQL returns if we provide an empty string for padding with other string in LPAD() or RPAD() functions?
• How can we use MySQL EXPORT_SET() function with column of a table?
• How can we update MySQL table after padding a string with the values of the column?
• What MySQL returns, if the length of the original string is greater than the length specified as an argument in LPAD() or RPAD() functions?
• How can we combine the values of two or more columns of MySQL table?
• How can we use MySQL POWER() function with the column’s data values?
• How can we use the output of LTRIM() and RTRIM() functions to update MySQL table?
• How can we update the values in one MySQL table by using the values of another MySQL table?
• What happens if the length of the original string is greater than the length of the string returned after padding in LPAD() or RPAD() functions?
• How can we add values into the columns of a MySQL table?
• How can we update MySQL table after removing a particular string from the values of column?
• How can we change the data type of the column in MySQL table?
• How can we count a number of unique values in a column in MySQL table?
• How can we subtract values in MySQL table with the help of LEFT JOIN?