Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQL Articles
Page 122 of 355
How can we import the text file, having data on the same line with a separator, into MySQL table?
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 MoreHow can we import only specific columns from the text file, into MySQL table?
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 MoreWhat is the difference between MySQL INSTR() and FIND_IN_SET() functions?
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 MoreHow it is possible in MySQL to find a string of specified pattern within another string?
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 MoreHow can we import data from .txt file into MySQL table?
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 MoreWhat happens if the value of number 'N' in CONV() function is not as per accordance with its base?
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.
Read MoreDo we have any lower and upper limit of base in MySQL CONV() function? What happens if out of limit base is provided in CONV() function?
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)
Read MoreHow can we import data from a text file having names of the columns in first row?
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 MoreHow LOCATE() function can be used with MySQL WHERE clause?
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 MoreWhat is the use of MySQL BINARY keyword while performing string comparison?
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