Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
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
We cannot use = (comparison operator) because we know that NULL is not a value. If we want to get the rows with NULL from a table then we need to use IS NULL operator in MySQL query. Following example using the data from ‘employee’ table will exhibit it −Examplemysql> Select * from Employee WHERE Salary IS NULL; +----+-------+--------+ | ID | Name | Salary | +----+-------+--------+ | 7 | Aryan | NULL | | 8 | Vinay | NULL | +----+-------+--------+ 2 rows in set (0.00 sec)The query above use IS NULL operator and produces ... Read More
MySQL INSERT() function performs no insertion if the position of insertion is out of range. The position of insertion can be out of range in the case when we pass a negative or 0(zero) value or the value goes beyond the value of a total number of characters in an original string by 2. It can be understood with the help of the following example −ExampleThe query below will perform no insertion because the position of insertion is out of range i.e. a negative value.mysql> Select INSERT('Virat', -1, 5, 'Kohli'); +-------------------------------+ | INSERT('Virat', -1, 5, 'Kohli') | +-------------------------------+ | Virat ... Read More
We can use the conditions in WHERE clause while exporting the data from MySQL table to a file. It can be understood with the help of an example −ExampleSuppose we are having following data from table ‘Student_info’ −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 125 | Raman | Shimla | Computers | | 130 | Ram | Jhansi | ... Read More