Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
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.
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
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
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
MySQL IF ELSE statement implements a basic conditional construct when the expression evaluates to false. Its syntax is as follows −IF expression THEN statements; ELSE else-statements; END IF;The statements must end with a semicolon.To demonstrate the use of IF 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 | History | | 105 | ... Read More
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
To make it understand we are using the table named ‘student_info’ which have the following values −mysql> Select * from student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Jaipur | Literature | | 110 | Rahul | Chandigarh | History | | 125 | Raman | Shimla | Computers | +------+---------+------------+------------+ 4 rows in set (0.00 sec)Now, with the help of the following query, we will create a stored ... Read More
We can create a stored procedure with IN and OUT operators to SELECT records, based on some conditions, from MySQL table. To make it understand we are taking an example of a table named ‘student_info’ having the following data −mysql> Select * from student_info; +------+---------+------------+------------+ | id | Name | Address | Subject | +------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Jaipur | Literature | | 110 | Rahul | Chandigarh | History | | 125 | Raman | Bangalore | ... Read More
Bit-field notation is the notation with the help of which we can write bit-field values. The syntax of Bit-field notation is as follows −Syntaxb’value’ OR 0bvalueHere, the value is a binary value written by using zeros and ones.The mainly Bit-filed notation is convenient for specifying values to be assigned to BIT columns of MySQL table. Following example will demonstrate it −mysql> Create table bit_testing (bittest BIT(8)); Query OK, 0 rows affected (1.09 sec) mysql> INSERT INTO bit_testing SET bittest = b'10101010'; Query OK, 1 row affected (0.07 sec) mysql> INSERT INTO bit_testing SET bittest = b'0101'; Query ... Read More