MySQL IF ELSEIF ELSE execute the statements based on multiple expressions Its syntax is as follows −IF expression THEN    statements; ELSEIF elseif-expression THEN    elseif-statements; … … … … ELSE   else-statements; END IF;The statements must end with a semicolon.To demonstrate the use of IF ELSEIF ELSE statement within MySQL stored procedure, we are creating the following stored procedure which is based on the values, as shown below, of the table named ‘student_info’ −mysql> Select * from student_info; +------+---------+------------+------------+ | id   | Name    | Address    | Subject    | +------+---------+------------+------------+ | 101  | YashPal | Amritsar   ... Read More

The IGNORE_SPACE SQL mode can be used to modify how the parser treats function names that are whitespace-sensitive. Following are the cases in which we can use IGNORE_SPACE SQL mode −Case-1  − When IGNORE_SPACE SQL mode is disabledAfter disabling the IGNORE_SPACE SQL mode, the parser interprets the name as a function call when there is no whitespace between the name and the following parenthesis. This also occurs when the function name is used in a non-expression context. It can be understood from the following query −mysql> Create table SUM(Id Int); ERROR 1064 (42000): You have an error in your SQL ... Read More

The default rules used by the parser for parsing names of built-in-function can be changed by enabling IGNORE_SPACE SQL mode. When we enable this mode, the parser relaxes the requirement that there be no whitespace between the function name and the following parenthesis. For example, after enabling IGNORE_SPACE SQL mode both of the following function calls are legal −Select SUM(Salary) from employee; Select SUM (Salary) from employee;But, in this case, the parser treats the function name as reserved words. It means that a space following the names no longer represents as an identifier.

MySQL IF statement implements a basic conditional construct within a stored procedure. Its syntax is as follows −IF expression THEN Statements; END IF;It must end with a semicolon. To demonstrate the use of IF statement within MySQL stored procedure, we are creating the following stored procedure which is based on the values, as shown below, of the table named ‘student_info’ −mysql> Select * from student_info; +------+---------+------------+------------+ | id   | Name    | Address    | Subject    | +------+---------+------------+------------+ | 101  | YashPal | Amritsar   | History    | | 105  | Gaurav  | Jaipur     | ... Read More

Actually, when a parser encounters a word that is the name of a built-in function, it must determine whether the name represents a function call or is instead a non-expression reference to an identifier such as a table or column name. consider the following queries −1. Select sum(salary) from employee; 2. Create table sum (i int);In the first query SUM is a reference to a function call and in the second query, it is referencing to table name.Parser follows the following rules to distinguish whether their names are being used as function calls or as identifiers in non-reference context −Rule1 ... Read More

MySQL SUBSTRING_INDEX() function will return the same string as output if the argument ‘count’ has the value greater than the total number of occurrences of delimiter. It can be demonstrated with the following example −mysql> Select SUBSTRING_INDEX('My Name is Ram','a',3); +-----------------------------------------+ | SUBSTRING_INDEX('My Name is Ram','a',3) | +-----------------------------------------+ | My Name is Ram                          | +-----------------------------------------+ 1 row in set (0.00 sec)The above query returns the same string because 3 is greater than the total number of occurrences of delimiter provided as argument i.e. ‘a’. There are only two ‘a’ in the string.

In MySQL stored procedure, user variables are referenced with an ampersand i.e. @, prefixed to the user variable names. For example, @A, @B, etc. are user variables. To demonstrate it, we are creating the following procedure −mysql> DELIMITER // ; mysql> CREATE PROCEDURE Proc_Uservariables()    -> BEGIN    -> SET @A = 100;    -> SET @B = 500;    -> SELECT @A,@B,@A+@B;    -> END // Query OK, 0 rows affected (0.00 sec) mysql> Delimiter ; // mysql> CALL Proc_Uservariables(); +------+------+-------+ | @A   | @B   | @A+@B | +------+------+-------+ |  100 |  500 |   600 | +------+------+-------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)

As we know that the default type of a bit values assigned to user variables is binary strings but we can also assign a bit value to a number by using the following two methods −By using CAST() functionWith the help of CAST(… AS UNSIGNED) the bit value can be assigned a number. The following example will illustrate it −mysql> SET @abc = CAST(0b1000011 AS UNSIGNED); Query OK, 0 rows affected (0.00 sec) mysql> Select @abc; +------+ | @abc | +------+ | 67   | +------+ 1 row in set (0.00 sec)By adding 0(+0)The bit value can be assigned ... Read More

By default, the bit values assigned to the user variables are binary strings. It can be illustrated by assigning the bit value to a user variable and then by retrieving them as follows −mysql> SET @abc = 0b1000011; Query OK, 0 rows affected (0.00 sec) mysql> Select @abc; +------+ | @abc | +------+ | C    | +------+ 1 row in set (0.00 sec)The above result set shows that the default type of a bit value assigned to user variables are binary strings.

Actually, Bit values are returned as binary values but we can also display them in the printable form with the help of following −By adding 0We can display Bit values in printable form by adding 0 to them. Following the example from the bit_testing table can be used to understand it −mysql> Select bittest+0 from bit_testing; +-----------+ | bittest+0 | +-----------+ |       170 | |         5 | |         5 | +-----------+ 3 rows in set (0.00 sec)By using conversion function BIN(), OCT(), HEX()We can also display Bit values in ... Read More