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

We can use LCASE() and LOWER() functions for changing the character case of a string to lower case and UCASE() and UPPER() functions for changing the character case of a string to upper case.Examplemysql> Select LCASE('NEW DELHI'); +--------------------+ | LCASE('NEW DELHI') | +--------------------+ | new delhi          | +--------------------+ 1 row in set (0.00 sec) mysql> Select LOWER('NEW DELHI'); +--------------------+ | LOWER('NEW DELHI') | +--------------------+ | new delhi          | +--------------------+ 1 row in set (0.00 sec) mysql> Select UCASE('new delhi'); +--------------------+ | UCASE('new delhi') | +--------------------+ ... 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

Actually, we can write the data on the same line in the text file by using a separator. In this case, while importing this text file into MySQL table then we must have to use ‘LINES TERMINATED BY’ option. It can be understood with the help of the following example −Suppose we are using ‘|’ as the LINE TERMINATOR symbol in a text file as follows −id, Name, Country, Salary|105, Chum*, Marsh, USA, 11000|106, Danny*, Harrison, AUS, 12000Now while importing this text file into MySQL table then we need to mention ‘LINE TERMINATED BY’ option also in the query as ... Read More

When we use INSTR() function with MySQL WHERE clause, we need to provide column name of the table as the first argument and the substring as 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

Back-slash(\) is the by default escape character for the MySQL and when we use it in the text file then we do not need to mention it in the query while importing the data from text file to table. But if we use any other character as escape character then it must be mentioned by using ESCAPED BY option in the query while importing the text file into a table. It can be understood with the help of the following example −Suppose we are using star symbol (‘* ‘) as the escape character in a text file as follows −id, ... Read More

Use of escape character (\) would become very essential when we want to insert a comma or any other character between the values of a filed. It can be understood with the help of an example. Suppose we want to import the data from a text file named A.txt, having the following data, into a MySQL table −id,   Name,    Country,       Salary 105,  Chum,    Marsh, USA,     11000 106,  Danny,   Harrison, AUS,  12000Here, we can see that the filed name has two values first name, last name separated by a comma. Now, the ... Read More