With the help of MySQL REPEAT() function, we can replicate a string for a specified number of times.SyntaxREPEAT(Str, No.)Here Str is the string which is to be replicated for a specified number of times.No. is the numerical value which indicates that how many times the string would be repeated.Examplemysql> Select REPEAT('#*',5); +----------------+ | REPEAT('#*',5) | +----------------+ | #*#*#*#*#*     | +----------------+ 1 row in set (0.00 sec)

MySQL returns NULL if the argument of QUOTE() function is NULL.Examplemysql> Select QUOTE(NULL); +-------------+ | QUOTE(NULL) | +-------------+ | NULL         +-------------+ 1 row in set (0.00 sec) mysql> Select Name, QUOTE(NULL) from student where id = 1; +--------+-------------+ | Name   | QUOTE(NULL) | +--------+-------------+ | Gaurav | NULL        | +--------+-------------+ 1 row in set (0.08 sec)

When QUOTE() function used with WHERE clause then the output depends upon the comparison values returned by WHERE clause. Following example will exhibit it −Examplemysql> Select Name, ID, QUOTE(Subject)AS Subject from Student WHERE Subject = 'History'; +-------+------+-----------+ | Name  | ID   | Subject   | +-------+------+-----------+ | Aarav | 2    | 'History' | +-------+------+-----------+ 1 row in set (0.00 sec)

MySQL QUOTE() function can be used to append values of a column with single quotes. For this, we must have to pass column name as the argument of QUOTE() function. Data from ‘Student’ table is used to demonstrate it as follows Example mysql> Select Name, ID, QUOTE(Subject)AS Subject from Student; +---------+------+-------------+ | Name | ID | Subject | +---------+------+-------------+ | Gaurav | 1 | 'Computers' | | Aarav | 2 | 'History' | | Harshit | 15 ... Read More

If we want to copy tables or databases from one MySQL server to another, then use the mysqldump with database name and table name.Run the following command at the source host. This will dump the complete database into dump.txt file.$ mysqldump -u root -p database_name table_name > dump.txt password *****We can copy complete database without using a particular table name as explained above.Now, ftp dump.txt file on another host and use the following command. Before running this command, make sure we have created database_name on the destination server.$ mysql -u root -p database_name < dump.txt password *****Another way to accomplish this without ... Read More

Suppose if we have dumped multiple databases or all the databases and now want to restore it then we can do it with the following example −C:\mysql\bin>mysql -u root < tutorials_query1.sqlWith the help of above query, we are restoring the dumped multiple databases named ‘tutorials’ and ‘query1’, which are dumped in the file named ‘tutorials_query1.sql’. In this case, we do not need to write the name of the database.Similarly, with the help of the following query, we can restore all the databases dumped by mysqldump −C:\mysql\bin>mysql -u root < alldatabases.sqlWith the help of above query, we are restoring all the ... Read More

As all of these functions are used to return the position of a substring within a string but LOCATE() function is a bit different from POSITION() and INSTR() function. In both POSITION() AND INSTR() functions, we cannot manage the starting position of search with the help of argument as position argument in LOCATE() function. All of these functions are having a difference in syntax also.

Suppose if we have dumped the whole database and now want to restore it then we can do it with the following example −C:\mysql\bin>mysql -u root query < tutorials.sqlWith the help of above query, we are restoring the dumped database named ‘tutorials’, in the file tutorials.sql, into other database named ‘query’. It means that the tables of database ‘tutorials’ would be restored in a database named ‘query’.

By using mysql dump client program we can take the backup of all the databases into a file having the extension ‘.sql’. It can be understood with the help of the following example −ExampleIn this example, with the help of mysql dump client program, we are taking the backup all the databases in a file named ‘alldatabases.sql’. The following command will do this −C:\mysql\bin>mysqldump -u root --all-databases > alldatabases.sqlThe above command will create a file named alldatabases.sql which has the dump information of all the databases.

We need to pass the column name as the argument of OCTET_LENGTH() function to count the number of characters stored in a data column. It displays the number of characters when referenced in SELECT clause. It can also be used as comparison value to decide whether or not the row should returned by using it in WHERE clause. The contents of ‘Student’ table are used to demonstrate it −mysql> Select Name, OCTET_LENGTH(Name)As 'Str_Length' from Student; +---------+------------+ | Name    | Str_Length | +---------+------------+ | Gaurav  | 6          | | Aarav   | 5       ... Read More