When MySQL performs string comparison then it is not case-sensitive but with the help of BINARY keyword, MySQL can perform case-sensitive string comparison. It is because BINARY keyword instructs MySQL to compare the characters in the string using their underlying ASCII values rather than just their letters. It can be illustrated with the following example from table ‘Student_info’ having the following data −mysql> Select * from student_info; +------+---------+------------+------------+ | id   | Name    | Address    | Subject    | +------+---------+------------+------------+ | 101  | YashPal | Amritsar   | History    | | 105  | Gaurav  | Chandigarh | ... Read More

When we use LOCATE() function with MySQL WHERE clause, we need to provide the substring as first argument and column name of the table as the second argument along with a comparison operator. Following is an example using ‘Student’ table to demonstrate it −ExampleSuppose we have the following values in ‘Student’ table −mysql> Select * from Student; +------+---------+---------+-----------+ | Id   | Name    | Address | Subject   | +------+---------+---------+-----------+ | 1    | Gaurav  | Delhi   | Computers | | 2    | Aarav   | Mumbai  | History   | | 15   | Harshit ... Read More

MySQL LOCATE() function makes it possible to find the location of the first occurrence of a substring in a string. Following is the syntax of using it −SyntaxLOCATE(Substring, String)In this function, Substring is the string whose position of occurrence needs to find and the string is a string from which the occurrence of substring needs to be searched.We must have to pass both the strings (i.e. substring, which is to be searched and the string, from which substring is to be searched) as arguments of the LOCATE() function.Examplemysql> Select LOCATE('Good', 'RAM IS A GOOD BOY')As Result; +--------+ | Result | ... Read More

Sometimes, the input text file has the names of the columns in the first row and to import data from such kind of text file to MySQL table we need to use ‘IGNORE ROWS’ option. To illustrate it we are using the following example −ExampleFollowings are the comma separated values in A.txt file −Id, Name, Country, Salary 100, ”Ram”, ”INDIA”, 25000 101, ”Mohan”, ”INDIA”, 28000We want to import this data into the following file named employee3_tbl −mysql> Create table employee3_tbl(Id Int, Name Varchar(20), Country Varchar(20), Salary Int); Query OK, 0 rows affected (0.1 sec)Now, the transfer of data from a ... Read More

Sometimes the input text files have the text fields enclosed by double quotes and to import data from such kind of files we need to use the ‘ENCLOSED BY’ option with LOAD DATA INFILE statement. We are considering the following example to make it understand −ExampleFollowings are the comma-separated values in A.txt file −100, ”Ram”, ”INDIA”, 25000 101, ”Mohan”, ”INDIA”, 28000We want to import this data into the following file named employee2_tbl −mysql> Create table employee2_tbl(Id Int, Name Varchar(20), Country Varchar(20), Salary Int); Query OK, 0 rows affected (0.1 sec)Now, the transfer of data from a file to a ... Read More

The base must be greater than 2 and less than 36 i.e. the lower limit of a base is 2 and the upper limit is 36. It is applied to both from_base and to_base values. If in case we provide out of limit values of the base then MySQL returns NULL as the output. Following example will demonstrate it −Examplemysql> Select CONV(10,10,38); +----------------+ | CONV(10,10,38) | +----------------+ | NULL           | +----------------+ 1 row in set (0.00 sec) mysql> Select CONV(10,72,2); +---------------+ | CONV(10,72,2) | +---------------+ | NULL          | +---------------+ 1 row in set (0.00 sec) mysql> Select CONV(10,10,1); +---------------+ | CONV(10,10,1) | +---------------+ | NULL          | +---------------+ 1 row in set (0.00 sec)

MySQL returns 0 as output if the number given in CONV() function is not as per accordance with its base. Suppose, if we want to convert decimal number 9 into the number in binary number system then in this case the value of from_base must be 10 but if we provide 8 as the value of from_base then MySQL returns 0 as output.Examplemysql> Select CONV(9,8,2); +-------------+ | CONV(9,8,2) | +-------------+ | 0           | +-------------+ 1 row in set (0.00 sec)As we know that the values of octal number system must be between 0 to 7, hence the number system for number 9 must be decimal i.e. 10.

It can be done with the help of LOAD DATA INFILE statement. To illustrate the concept we are having the following data, separated by tab, in ‘A.txt’ whose path is d:/A.txt −100 John  USA 10000 101 Paul  UK  12000 102 Henry NZ  11000 103 Rick  USA 17000 104 Corey USA 15000We want to load the data of A.txt into the following table named employee_tbl −mysql> Create table employee_tbl(Id Int, Name varchar(20), Country Varchar(20), Salary Int); Query OK, 0 rows affected (0.91 sec)Now, the transfer of data from a file to a database table can be done with the help ... Read More

Transferring the information between MySQL and data files mean importing data from data files into our database or exporting data from our database into files. MySQL is having two statements that can be used to import or export data between MySQL and data files −LOAD DATA INFILEThis statement is used for importing the data from data files into our database. It reads data records directly from a file and inserts them into a table. Its syntax would be as follows −SyntaxLOAD DATA LOCAL INFILE '[path/][file_name]' INTO TABLE [table_name ];Here, the path is the address of the file.file_name is the name ... Read More

MySQL will return NULL as the output if any of the argument of CONV() function is NULL or if the value provided for the base is out of limit(i.e. not between minimum 2 and maximum 36). Following examples would demonstrate it. Example mysql> Select CONV(10,NULL,2); +-----------------+ | CONV(10, NULL,2)| +-----------------+ | NULL | +-----------------+ 1 row in set (0.00 sec) mysql> Select CONV(10,10, NULL); +------------------+ | CONV(10,10, NULL)| +------------------+ | NULL | +------------------+ 1 row in set (0.00 sec) mysql> Select CONV(NULL,10,2); +-----------------+ | CONV(null,10,2) | +-----------------+ | NULL | +-----------------+ 1 row in set (0.00 sec)