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

MySQLMySQLi Database

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)
raja
Published on 20-Feb-2018 13:11:37
Advertisements