MySQLi Articles - Page 318 of 341

While exporting the data from MySQL table to a text file we can use ‘FIELDS TERMINATED BY’, ‘ENCLOSED BY’, ‘LINES TERMINATED BY’ and other options too to put the values of fields in different settings of the text file. It can be illustrated with the help of the following 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 | | ... 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

Suppose if we want to upload the changed value rather than the value written in a text file then we need to use user variables along with the SET command. It can be understood with the help of the following example −ExampleSuppose we are having the following data in ‘A.txt’ −105, Chum, USA, 11000 106, Danny, AUS, 12000But we want to upload the value of salary after adding 500 to it at the time of importing it without changing the value of salary in a text file then it can be done with the help of the following query by ... Read More

We can use MySQL INSERT() function to insert new string within a string after removing the characters from the original string.SyntaxINSERT(original_string, @pos, @len, new_string)Here, original_string is the string in which we want to insert new string at the place of some specific number of characters.@pos is the position at which the insertion of new string should start.@len is the number of characters should delete from the original string. The starting point of deletion of characters is value of @pos.New_string is the string we want to insert into the original string.Examplemysql> Select INSERT('Yash Sharma', 5, 7, ' Pal'); +----------------------------------+ | ... Read More

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

MySQL returns a specified number of characters of a string with the help of LEFT() and RIGHT() functions.MySQL LEFT() function will return the specified number of characters from the left of the string.SyntaxLEFT(str, length)Here str is the string from which a number of characters would be returned and the length is an integer value which specifies how many characters to be returned.Examplemysql> Select LEFT('My Name is Ram', 7); +---------------------------+ | LEFT('My Name is Ram', 7) | +---------------------------+ | My Name                   | +---------------------------+ 1 row in set (0.00 sec)MySQL RIGHT() function will ... Read More

We can find a string of specified pattern within another string by using LIKE operator along with WILDCARDS.SyntaxLIKE specific_patternSpecific_pattern is the pattern of string we want to find out within another string.ExampleSuppose we have a table named ‘student’ having names of the students and we want to get the details of all those students which are having the pattern of string ‘av’ within their names. It can be done with the help of following MySQL query −mysql> Select * from Student Where Name LIKE '%av%'; +------+--------+---------+-----------+ | Id   | Name   | Address | Subject   | +------+--------+---------+-----------+ ... Read More

As we know, both the functions are used to search a string from the arguments provided in them but there are some significant differences between them as followsFIND_IN_SET() function uses the string list that is itself a string containing the substring separated by commas. Whereas, INSTR() function contains a string from which it will find the position of the first occurrence of the substring if present. In case of integers, FIND_IN_SET() is much more suitable than INSTR() function. It can be understood by the following exampleExamplemysql> Select IF(INSTR('10, 11, 12, 13', 2) > 0, 1, 0) As Result; +--------+ | Result ... Read More

Suppose if we have the values for some specific columns in the text file and MySQL table, in which we want to import the data, is having an extra column(s) then by mentioning the names of the columns in the query we can upload the values of those specific columns only. It can be understood with the help of the following example −ExampleSuppose we are having the values of columns ‘id’, ‘Name’ and ‘Salary’ only in the text file as follows −105, Chum, 11000 106, Danny, 12000Now while importing this text file into MySQL table then we need to mention ... Read More

Suppose if we have a line prefix in the text file then with the help of using ‘LINES STARTING BY’ option we can ignore that prefix and import correct data into MySQL table. It can be understood with the help of the following example −ExampleSuppose we are using ‘VALUE’ as the ‘LINE PREFIX’ in the text file as follows −id,         Name,     Country,        Salary VALUE:105,  Chum*,    Marsh, USA,      11000 106,        Danny*,   Harrison, AUS,   12000Now while importing this text file into MySQL table then we ... Read More