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
MySQLi Articles
Page 107 of 341
How can we store any other value than N in CSV file if we export the data to CSV file from a table which contains a NULL value(s)?
If we want to store any other value than \N in CSV file on exporting the data to CSV file from a table which contains NULL value(s) then we need to replace \N values with other value by using IFNULL statement. To illustrate it we are taking the following example −ExampleSuppose if we want to export the values of the table ‘student_info’ having the following data −mysql> Select * from Student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | ...
Read MoreHow can we tackle MySQL error 'ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement' while importing or exporting the data?
MySQL throws this error because of the two reasons, either no directory is specified under --secure--file--priv variable or we are giving the wrong path in our query while importing or exporting the data. To tackle this error we must have to check the value of –secure—file—priv variable by following query −mysql> Select @@global.secure_file_priv; +---------------------------+ | @@global.secure_file_priv | +---------------------------+ | C:\mysql\bin\mysql-files\ | +---------------------------+ 1 row in set (0.00 sec)We can see there is path under secure_file_priv variable and all the files would be created under this directory when we export the data.But, if the above command shows NULL as result then ...
Read MoreWhich MySQL function returns a specified number of characters of a string as output?
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 MoreWhat is the maximum length of each type of identifier in MySQL?
As we know that certain objects within MySQL are known as identifiers. These objects include a database, table, index, column, alias, view, stored procedure, partition, tablespace etc. Identifiers are stored using Unicode (UTF-8). The maximum length of each type of identifier is given in the following table:Sr. No.IdentifierMaximum Length (characters)1Database642Table643Column644Index645Constraint646Stored Procedure or Function647Trigger648View649Event6410Tablespace6411Log File Group6412Alias25613Compound Statement Label16
Read MoreHow can we use LPAD() or RPAD() functions with the values in the column of a MySQL table?
For using LPAD() or RPAD() functions with the column values we need to specify the column name as the first argument of these functions. Following the example from ‘Student’ table will make it clearer −Examplemysql> Select Name, LPAD(Name, 10, '*') from student; +---------+-------------------+ | Name | LPAD(Name, 10, '*') | +---------+-------------------+ | Gaurav | ****Gaurav | | Aarav | *****Aarav | | Harshit | ***Harshit | | Gaurav | ****Gaurav ...
Read MoreHow can we use the output of LTRIM() and RTRIM() functions to update MySQL table?
We can use LTRIM() and RTRIM functions with MySQL update clause so that the values, after removing space characters, in the table can be updated. Following examples will demonstrate it −ExampleSuppose we know that there can be some space characters in the values of ‘Name’ column of table ‘Student’ then with the help of following single query we can remove the space characters from that column’s value and also update the table −mysql> Update Student SET Name = LTRIM(Name); Query OK, 0 rows affected (0.07 sec) Rows matched: 5 Changed: 0 Warnings: 0 mysql> Update Student SET Name = ...
Read MoreHow can I remove the leading and trailing spaces both at once from a string without using MySQL LTRIM() and RTRIM() functions?
Other than LTRIM() and RTRIM() functions, MySQL has TRIM() function to remove leading and trailing function both at once from a string. The use of TRIM() function can be understood from the following example of a test_trim table which has a column ‘Name’ containing the names with leading and trailing spaces.Examplemysql> Select Name, TRIM(Name)AS 'Name Without Spaces' from test_trim; +---------------+---------------------+ | Name | Name Without Spaces | +---------------+---------------------+ | Gaurav | Gaurav | | Rahul | Rahul | | Aarav | Aarav | +---------------+---------------------+ 3 rows in set (0.00 sec)
Read MoreHow can we use MySQL TRIM() to remove the whitespaces from all the rows and update table?
Suppose if a table has many values having whitespaces in the columns of a table then it is wastage of space. We can use TRIM() function to remove whitespaces from all the rows and update the table too in a single query. Following the example from ‘Employee’, having whitespaces in all its rows will exhibit the concept −Examplemysql> Select * from Employee; +------+----------------------+----------------------+----------------------+ | Id | Name | Address | Department | +------+----------------------+----------------------+----------------------+ | 100 | Raman ...
Read MoreWhat is the significance of using multiple columns in MySQL GROUP BY clause?
By specifying multiple columns in GROUP BY clause we can split the result set into smaller groups. The more columns specified in GROUP BY clause, the smaller the groups will be.Examplemysql> Select designation, YEAR(Doj), count(*) from employees GROUP BY designation, YEAR(DoJ); +-------------+-----------+----------+ | designation | YEAR(Doj) | count(*) | +-------------+-----------+----------+ | Asso.Prof | 2013 | 1 | | Asst.Prof | 2015 | 1 | | Asst.Prof | 2016 | 1 | | Prof | 2009 | 2 | | Prof | 2010 | 1 | +-------------+-----------+----------+ 5 rows in set (0.00 sec)
Read MoreIn MySQL, how to remove the specific prefix from entire column's value and update them?
It can be done by applying TRIM() function on the column along with MySQL UPDATE statement. The example below will make it more clear.ExampleSuppose, we have a table ‘Employee’ which have the prefix ‘Dept.’ with all the values of Column ‘Department’ as follows −mysql> Select * from Employee; +------+----------------+------------+----------------------+ | Id | Name | Address | Department | +------+----------------+------------+----------------------+ | 100 | Raman | Delhi | IT Dept. | | 101 | Mohan ...
Read More