Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Instance VariablesA variable which is declared inside a class and outside all the methods and blocks is an instance variable. The general scope of an instance variable is throughout the class except in static methods. The lifetime of an instance variable is until the object stays in memory.Class VariablesA variable which is declared inside a class, outside all the blocks and is marked static is known as a class variable. The general scope of a class variable is throughout the class and the lifetime of a class variable is until the end of the program or as long as the ... Read More
In this case, MySQL will not pad anything and truncate the characters from the original string up to the value of length provided as the argument in LPAD() or RPAD() functions.Examplemysql> Select LPAD('ABCD',3,'*'); +--------------------+ | LPAD('ABCD',3,'*') | +--------------------+ | ABC | +--------------------+ 1 row in set (0.00 sec) mysql> Select RPAD('ABCD',3,'*'); +--------------------+ | RPAD('ABCD',3,'*') | +--------------------+ | ABC | +--------------------+ 1 row in set (0.00 sec)We can observe from the above example that both the functions do not pad ‘*’ and truncate the original string up to the length specified i.e. 3 as the argument.
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 −Examplemysql> 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 ... Read More
We can use LTRIM() and RTRIM functions with MySQL update clause so that the values, after removing space characters, in the table can be updated. Following examples will demonstrate it −ExampleSuppose we know that there can be some space characters in the values of ‘Name’ column of table ‘Student’ then with the help of following single query we can remove the space characters from that column’s value and also update the table −mysql> Update Student SET Name = LTRIM(Name); Query OK, 0 rows affected (0.07 sec) Rows matched: 5 Changed: 0 Warnings: 0 mysql> Update Student SET Name = ... Read More
MySQL LTRIM() and RTRIM() functions can be used to eradicate leading and trailing spaces from a string.MySQL LTRIM() function is used to remove the leading space characters from a string. Its syntax can be as follows −SyntaxLTRIM(String)Here, String, is the string, passed as an argument, whose leading space characters are to be removed.Examplemysql> Select LTRIM(' Hello'); +--------------------+ | LTRIM(' Hello') | +--------------------+ | Hello | +--------------------+ 1 row in set (0.00 sec)MySQL RTRIM() function is used to remove the trailing space characters from a string. Its syntax can be as follows −SyntaxRTRIM(String)Here, String, ... Read More
For removing both leading and trailing spaces at once from a string by using LTRIM() and RTRIM() functions, we must have to use one function as an argument for other function. In other words, we must have to pass either LTRIM() function as an argument of RTIM() function or vice versa. It can be understood from the following example −ExampleSuppose we have a table ‘test_trim’ having a column ‘Name’ containing the values with leading and trailing spaces both −mysql> Select * from test_trim; +---------------+ | Name | +---------------+ | Gaurav | | Rahul ... Read More
Other than LTRIM() and RTRIM() functions, MySQL has TRIM() function to remove leading and trailing function both at once from a string. The use of TRIM() function can be understood from the following example of a test_trim table which has a column ‘Name’ containing the names with leading and trailing spaces.Examplemysql> Select Name, TRIM(Name)AS 'Name Without Spaces' from test_trim; +---------------+---------------------+ | Name | Name Without Spaces | +---------------+---------------------+ | Gaurav | Gaurav | | Rahul | Rahul | | Aarav | Aarav | +---------------+---------------------+ 3 rows in set (0.00 sec)
Suppose if a table has many values having whitespaces in the columns of a table then it is wastage of space. We can use TRIM() function to remove whitespaces from all the rows and update the table too in a single query. Following the example from ‘Employee’, having whitespaces in all its rows will exhibit the concept −Examplemysql> Select * from Employee; +------+----------------------+----------------------+----------------------+ | Id | Name | Address | Department | +------+----------------------+----------------------+----------------------+ | 100 | Raman ... Read More
By specifying multiple columns in GROUP BY clause we can split the result set into smaller groups. The more columns specified in GROUP BY clause, the smaller the groups will be.Examplemysql> Select designation, YEAR(Doj), count(*) from employees GROUP BY designation, YEAR(DoJ); +-------------+-----------+----------+ | designation | YEAR(Doj) | count(*) | +-------------+-----------+----------+ | Asso.Prof | 2013 | 1 | | Asst.Prof | 2015 | 1 | | Asst.Prof | 2016 | 1 | | Prof | 2009 | 2 | | Prof | 2010 | 1 | +-------------+-----------+----------+ 5 rows in set (0.00 sec)
It can be done by applying TRIM() function on the column along with MySQL UPDATE statement. The example below will make it more clear.ExampleSuppose, we have a table ‘Employee’ which have the prefix ‘Dept.’ with all the values of Column ‘Department’ as follows −mysql> Select * from Employee; +------+----------------+------------+----------------------+ | Id | Name | Address | Department | +------+----------------+------------+----------------------+ | 100 | Raman | Delhi | IT Dept. | | 101 | Mohan ... Read More