String-valued functions return NULL if the length of the result would be greater than the value of the max_allowed_packet system variable. Actually, max_allowed_packet is a dynamic global variable which can accept the integer type values. These values can be set for a session only. It can accept 1024 as the minimum value and 1073741824 as the maximum value. The by the default value of this system variable is 1048576.

MySQL CONCAT() function will return a NULL if you will add a NULL value while linking two strings. Following example will demonstrate it −Examplemysql> Select CONCAT('Tutorials',NULL,'Point'); +----------------------------------+ | CONCAT('Tutorials',NULL,'Point') | +----------------------------------+ | NULL                             | +----------------------------------+ 1 row in set (0.06 sec) mysql> Select CONCAT('TutorialsPoint','.com',NULL); +--------------------------------------+ | CONCAT('TutorialsPoint','.com',NULL) | +--------------------------------------+ | NULL                                 | +--------------------------------------+ 1 row in set (0.00 sec)

As we know that MySQL NULLIF() control flow function will return NULL if both the arguments are the same, otherwise it returns the first argument. Hence it is similar to the following CASE statement −CASE WHEN expression1=expression2 THEN NULL ELSE Expression2 END.

As we know that MySQL NULLIF() control flow function will return the first argument both the arguments are not the same. The first argument is returned because MySQL evaluates the first argument twice if both of the arguments are not the same.Examplemysql> Select NULLIF('Tutorialspoint','MySQL'); +----------------------------------+ | NULLIF('Tutorialspoint','MySQL') | +----------------------------------+ | Tutorialspoint                   | +----------------------------------+ 1 row in set (0.00 sec)In the above example, as the arguments are not the same hence MySQL evaluates the first argument i.e.’Tutorialspoint’ two times and return it as output.

MySQL NULLIF() control flow function will return NULL if both the arguments are the same, otherwise, it returns the first argument.SyntaxNULLIF(expression1, expression2)Here if expression1 = expression2, NULL will be returned by NULLIF() otherwise expression1 will be returned. Following example will exhibit this −mysql> Select NULLIF('Ram','Ram'); +---------------------+ | NULLIF('Ram','Ram') | +---------------------+ | NULL                | +---------------------+ 1 row in set (0.00 sec) mysql> Select NULLIF('Ram','Shyam'); +-----------------------+ | NULLIF('Ram','Shyam') | +-----------------------+ | Ram                   | +-----------------------+ 1 row in set (0.00 sec)

Suppose if we declare a variable inside a BEGIN/END block then the scope of this variable would be in this particular block. We can also declare a variable with the same name inside another BEGIN/END block which will be totally legal but its scope would be inside its BEGIN/END block. It can be understood with the help of the following example in which we are creating a procedure to show the scope of the variables −Examplemysql> Create Procedure Scope_variables()     -> BEGIN     -> DECLARE A Varchar(5) Default 'outer';     -> BEGIN     -> DECLARE A ... Read More

Actually, MySQL preserves the environment at the time the stored procedure is created. It can be understood with the help of following the example in which we are using two bars for concatenating strings. This is only legal while SQL mode is ansi. But if we change the SQL mode to non-ansi, the procedure still works as if the original setting is still true.Examplemysql> Set sql_mode = 'ansi'// Query OK, 0 rows affected, 1 warning (0.14 sec) mysql> Create Procedure Con_string()     -> SELECT 'a'||'b'// Query OK, 0 rows affected (0.12 sec) mysql> Call Con_string (); +----------+ | ... Read More

MySQL IFNULL() control flow function will return the first argument if it is not NULL otherwise it returns the second argument.SyntaxIFNULL(expression1, expression2)Here if expression1 is not NULL then IFNULL() will return expression1 otherwise expression2. It will return NULL if both of the arguments are NULL. Following example will exhibit this −mysql> Select IFNULL(NULL, 'Ram'); +--------------------+ | IFNULL(NULL, 'Ram') | +--------------------+ | Ram                | +--------------------+ 1 row in set (0.00 sec) mysql> Select IFNULL('Shyam', 'Ram'); +-----------------------+ | IFNULL('Shyam', 'Ram') | +-----------------------+ | Shyam                 | +-----------------------+ ... Read More

As we know that REPLACE () function is used to replace the occurrences of a substring with another substring within a string. We can also use the REPLACE function with the UPDATE statement to update the table. Following example will demonstrate it −Examplemysql> Update Student set Father_Name = REPLACE(Father_Name, 'Mr.', 'Shri '); Query OK, 5 rows affected (0.06 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> Select Name, Father_Name from Student; +---------+-----------------+ | Name    | Father_Name     | +---------+-----------------+ | Gaurav  | Shri Ramesh     | | Aarav   | Shri Sanjay     | ... Read More

We can create MySQL stored procedures without ‘BEGIN’ and ‘END’ just in the same way created with both of them only thing is to omit to BEGIN and END. In the following example, we are creating a stored procedure without ‘BEGIN’ and ‘END’ to get all the rows from a table −Examplemysql> Delimiter // mysql> CREATE PROCEDURE Hello()     -> SELECT * from Student_info; // Query OK, 0 rows affected (0.08 sec)We can see MySQL created stored procedures without BEGIN and END. Now invoke this by CALL statement −mysql> Delimiter ; mysql> CALL Hello(); +-----+---------+------------+------------+ | id  | ... Read More