Sometimes we need to export data into a CSV file whose name has a timestamp at which that file is created. It can be done with the help of MySQL prepared statement. To illustrate it we are using the following example −ExampleThe queries in the following example will export the data from table ‘student_info’ to the CSV file having a timestamp in its name.mysql> SET @time_stamp = DATE_FORMAT(NOW(), '_%Y_%m_%d_%H_%i_%s'); Query OK, 0 rows affected (0.00 sec) mysql> SET @FOLDER = 'C:/mysql/bin/mysql-files'; Query OK, 0 rows affected (0.00 sec) mysql> SET @FOLDER = 'C:/mysql/bin/mysql-files/'; Query OK, 0 rows affected ... Read More

MySQL SUM() function will return 0, rather than NULL, along with a warning on getting the character type column as its argument. Following example using data from table named ‘Social’ will illustrate it −Examplemysql> Select * from Social; +------+-------+ | Id   | Name  | +------+-------+ | 100  | Rahul | +------+-------+ 1 row in set (0.00 sec) mysql> Select SUM(Name) From Social; +-----------+ | SUM(Name) | +-----------+ | 0         | +-----------+ 1 row in set, 1 warning (0.00 sec)

When MySQL SUM() function got a column, having no values, an argument then it will return NULL, rather than 0, as output. But if we want to customize this output to show 0 as output then we can use MySQL COALESCE() function which accepts two arguments and returns the second argument if the first argument is NULL, otherwise, it returns the first argument. To illustrate it, we are taking the example of ‘Tender’ table having the following data −mysql> Select * from tender; +----+---------------+--------------+ | Sr | CompanyName   | Tender_value | +----+---------------+--------------+ | 1  | Abc Corp.   ... Read More

When MySQL SUM() function got a column, having no values, as an argument then it will return NULL, rather than 0, as output. The column can be of any data type. Following the example, using a table named ‘social’ having only one column named ‘id’ with no values, will illustrate itExamplemysql> Describe Social; +-------+-------------+------+-----+---------+-------+ | Field | Type        | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Id    | int(11)     | YES  |     |   NULL  |       | | Name  | varchar(20) | YES  |     ... Read More

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

MySQL TRIM() function is used to eradicate a specific suffix or prefix or both from the string. The working of TRIM() function can be understood with the help of its syntaxSyntaxTRIM([{BOTH | LEADING | TRAILING} [str_to_remove] FROM] string)Here,  The argument BOTH means the prefixes from both left and right to be removed from the string.LEADING argument means that only leading prefixes to be removed.TRAILING argument means that only trailing prefixes to be removed.Str_to_remove is the argument which means the string we want to remove from the string.String argument means the string from which the prefixes have to be removed.Examplemysql> Select ... Read More

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 More

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)

For removing both leading and trailing spaces at once from a string by using LTRIM() and RTRIM() functions, we must have to use one function as an argument for other function. In other words, we must have to pass either LTRIM() function as an argument of RTIM() function or vice versa. It can be understood from the following example −ExampleSuppose we have a table ‘test_trim’ having a column ‘Name’ containing the values with leading and trailing spaces both −mysql> Select * from test_trim; +---------------+ | Name          | +---------------+ | Gaurav        | | Rahul ... Read More

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 More