MySQL INSERT() function performs no insertion if the position of insertion is not within the length of the string. There are certain cases like we pass a negative or 0(zero) value or the value goes beyond the value of a total number of characters in an original string by 2 when we can say that ‘pos’ is not within the length of the string. It can be understood with the help of the following example −ExampleThe query below will perform no insertion because the ‘pos’ is not within the length of string i.e. a negative value.mysql> Select INSERT('Tutorialspoint', -1, 4, '.com'); +--------------------------------------+ ... Read More

We can use a MySQL INSERT() function to insert a substring at the specified position in a string.SyntaxINSERT(original_string, @pos, @len, new_string)Here, original_string is the string in which we want to insert a new string at the place of some specific number of characters.@pos is the position at which the insertion of the new string should start.@len is the number of characters that should delete from the original string. The starting point of the deletion of characters is the value of @pos.New_string is the string we want to insert into the original string.Examplemysql> Select INSERT('MySQL Tutorial', 7, 8, '@Tutorialspoint'); +------------------------------------------------+ | ... Read More

We can use the keyword USING to produce a string, other than default binary string, in a given character set. Following result set will demonstrate it −mysql> Select CHARSET(CHAR(85 USING utf8)); +------------------------------+ | CHARSET(CHAR(85 USING utf8)) | +------------------------------+ | utf8                         | +------------------------------+ 1 row in set (0.00 sec)The above result set shows that the returned binary string is utf8 because we write utf8 after the keyword USING.mysql> Select CHARSET(CHAR(85 USING latin1)); +--------------------------------+ | CHARSET(CHAR(85 USING latin1)) | +--------------------------------+ | latin1                 ... Read More

With the help of CHARSET() function, we can check which string is returned by MySQL CHAR() function. Following result set will demonstrate it − mysql> Select CHARSET(CHAR(85)); +-------------------+ | CHARSET(CHAR(85)) | +-------------------+ | binary | +-------------------+ 1 row in set (0.00 sec)

MySQL converts the arguments of CHAR() function which is greater than 255 to multiple result bytes. For example, CHAR(260) is equivalent to CHAR(0,1,0,4). It can be more clear with the help of following statements −mysql> Select HEX(CHAR(256)),HEX(CHAR(1,0)); +----------------+----------------+ | HEX(CHAR(256)) | HEX(CHAR(1,0)) | +----------------+----------------+ | 0100           | 0100           | +----------------+----------------+ 1 row in set (0.00 sec)The above result set shows that CHAR(256) is equivalent to CHAR(1,0).

As we know that MySQL provides us loop statements that allow us to execute a block of SQL code repeatedly based on a condition. A REPEAT loop statement is one of such kind of loop statement. Its syntax is as follows −REPEAT    statements; UNTIL expression END REPEATFirst of all, MySQL executes the statements, and then it evaluates the expression. If the expression evaluates to FALSE, MySQL executes the statements repeatedly until the expression evaluates to TRUE. The REPEAT loop checks the expression after the statements execute, that is why it is also called a post-test loop.To demonstrate the use of a ... Read More

As we know that MySQL provides us loop statements that allow us to execute a block of SQL code repeatedly based on a condition. WHILE loop statement is one of such kind of loop statements. Its syntax is as follows −WHILE expression DO statements END WHILEActually, the WHILE loop checks the expression at the starting of every iteration. If the expression evaluates to true, MySQL will execute statements between WHILE and END WHILE until the expression evaluates to false. The WHILE loop checks the expression before the statements execute, that is why it is also called the pretest loop.To demonstrate ... Read More

MySQL CHAR() function will ignore NULL if it is provided as an argument to it. To understand it, consider the following examples −mysql> Select CHAR(65,66,67,NULL); +---------------------+ | CHAR(65,66,67,NULL) | +---------------------+ | ABC                 | +---------------------+ 1 row in set (0.00 sec) mysql> Select CHAR(NULL,66,67,NULL); +-----------------------+ | CHAR(NULL,66,67,NULL) | +-----------------------+ | BC                    | +-----------------------+ 1 row in set (0.00 sec)In both the examples above, CHAR() function ignores the NULL and converts the numeric value into character value.

Actually, the CASE statement has the functionality of an IF-THEN-ELSE statement. It has the following syntax −CASE WHEN condition_1 THEN    {...statements to execute when condition_1 is TRUE...} [ WHEN condition_2 THEN    {...statements to execute when condition_2 is TRUE...} ] [ WHEN condition_n THEN    {...statements to execute when condition_n is TRUE...} ] [ ELSE    {...statements to execute when all conditions were FALSE...} ] END CASE;The CASE statement will execute the ELSE clause if none of the WHEN clauses were executed.To demonstrate the use of CASE statement within MySQL stored procedure, we are creating the following stored procedure which ... 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