Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
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
For this purpose, We need to use the name of the column as the first parameter i.e. at the place of an original string, of the INSERT() function. Following example will exhibit it −ExampleSuppose we want to add ‘/Old’ with the values of ‘year_of_admission’ column of ‘Student’ table then we need to write following query −mysql> Select INSERT(year_of_admission, 5, 0, '/Old')As 'Old Admissions' From Student; +-----------------+ | Old Admissions | +-----------------+ | 2001/Old | | 2010/Old | | 2009/Old | | 2017/Old | | 2000/Old ... Read More
In case if the number of characters to be removed exceeds the number of characters available in original string then MySQL INSERT() function will continue to remove the characters until the end of the original string.Examplemysql> Select INSERT('myteststring',3,15,'original'); +----------------------------------------+ | INSERT('myteststring',3,15,'original') | +----------------------------------------+ | myoriginal | +----------------------------------------+ 1 row in set (0.00 sec)
MySQL has two functions namely LPAD() and RPAD() with the help of which we can pad a string with another string.LPAD() function, as the name suggests, left pads a string with another string. Following is the syntax for using it in MySQLSyntaxLPAD(original_string, @length, pad_string)Here, original_string is the string in which we pad another string.@length is the total length of the string returned after padding.Pad_string is the string which is to be padded with original_string.Examplemysql> Select LPAD('My name is Ram', 22, '* '); +--------------------------------+ | LPAD('My name is Ram', 22, '* ') | +--------------------------------+ | * * * * My name ... Read More
For achieving this, we must have to use one of the functions as the 1st argument of the other function. In other words, either RPAD() function would be the 1st argument of LPAD() function or LPAD() function would be the 1st argument of RPAD() function. It can be understood with the help of the following exampleExamplemysql> Select RPAD(LPAD(' My name is Ram ', 23, '* '), 30, '* '); +------------------------------------------------+ | RPAD(LPAD(' My name is Ram ', 23, '* '), 30, '* ') | +------------------------------------------------+ | * * * * My name is Ram * * * * ... Read More
Suppose if we provide an empty string for padding on LPAD() or RPAD() functions then MySQL will return NULL as output. Following example will demonstrate it −Examplemysql> Select LPAD('ABCD',22,''); +--------------------+ | LPAD('ABCD',22,'') | +--------------------+ | NULL | +--------------------+ 1 row in set (0.00 sec) mysql> Select RPAD('ABCD',22,''); +--------------------+ | RPAD('ABCD',22,'') | +--------------------+ | NULL | +--------------------+ 1 row in set (0.00 sec)
Suppose if there is a blank line between two line written in the text file then MySQL evaluates it as the data line while importing that text file into MySQL table. It can be understood with the help of the following example −ExampleSuppose we are having a blank line between two lines in a text file named ‘A.txt’ as follows −105, Chum, USA, 11000 106, Danny, AUS, 12000Now we can write the following query to import the data from text file into MySQL table −mysql> LOAD DATA LOCAL INFILE 'd:\A.txt' INTO table employee10_tbl FIELDS TERMINATED BY ', '; Query OK, ... Read More
As we know that LIKE operator is used along with WILDCARD characters to get the string having specified string. Basically, WILDCARD is the characters that help search data matching complex criteria. Followings are the types of wildcard which can be used in conjunction with LIKE operator % -The PercentageThe ‘%’ wildcard is used to specify a pattern of 0, 1 or more characters. A basic syntax for using % wildcard is as followsSelect Statement…Where column_name LIKE ‘X%’Here, X, is any specified starting pattern such as the single character of more and % matches any number of characters starting from 0.The percentage ... Read More