Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQL Articles
Page 79 of 355
MySQL BIT_LENGTH() function is multi-byte safe or not?
Just like LENGTH() function, MySQL BIT_LENGTH() function is not a multi-byte safe function. As we know that the difference of the result between multi-byte safe functions, like CHAR_LENGTH() or CHARACTER_LENGTH(), and BIT_LENGTH() function especially relevant for Unicode, in which most of the characters are encoded in two bytes or relevant for UTF-8 where the number of bytes varies. It is demonstrated in the example below −Examplemysql> Select BIT_LENGTH('tutorialspoint'); +------------------------------+ | BIT_LENGTH('tutorialspoint') | +------------------------------+ | 112 ...
Read MoreHow can we update MySQL table after padding a string with the values of the column?
We can update MySQL table after padding a string with the values of a column by using LPAD() or RPAD() function along with UPDATE clause. Following the example from ‘examination_btech’ table will make it clearer −ExampleSuppose if we want to append the values, in last, of column course with the string ‘(CSE)’ and want to update the table too then it can be done with the help of the following query −mysql> Update examination_btech set course = RPAD(Course, 11, '(CSE)'); Query OK, 10 rows affected (0.16 sec) mysql> Select * from examination_btech; +-----------+----------+-------------+ | RollNo | Name ...
Read MoreHow can we use prepared statements in a stored procedure?
If we want to use prepared statements in a stored procedure then it must be written inside the BEGIN and END block. To understand it, we are creating an example with the help of which we can get all the records from a table by passing the name of the table as a parameter of the stored procedure.Examplemysql> DELIMITER // mysql> Create procedure tbl_detail(tab_name Varchar(40)) -> BEGIN -> SET @A:= CONCAT('Select * from', ' ', tab_name); -> Prepare stmt FROM @A; -> EXECUTE stmt; -> END // Query OK, 0 ...
Read MoreHow can we overcome the property of CONCAT() function that it returns NULL if any one of the argument is NULL, especially when we want to concatenate the values from the column and any of the columns have NULL as its value?
Problem Statement How can we overcome the property of CONCAT() function that it returns NULL if any one of the argument is NULL, especially when we want to concatenate the values from the column and any of the columns have NULL as its value? The above-said property is not useful especially in the case when we want to concatenate the values from the column and any of the columns have NULL as its value. To overcome this, we can use IFNULL() function along with CONCAT() function. To ...
Read MoreWhat do you mean by Scope of variables inside MySQL stored procedure?
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'; -> ...
Read MoreDoes MySQL preserve the environment at the time the stored procedure created?
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 MoreWhat is the advantage of CONCAT_WS() function over CONCAT() function when we want to concatenate the values from the column and any of the columns have NULL as its value?
As we know that CONCAT() function returns NULL if any of the arguments is NULL but CONCAT_WS() function returns NULL only if the first argument i.e. the separator is NULL and it ignores any other NULL. We can say this is the advantage of CONCAT_WS() function over CONCAT() function when we want to concatenate the values from the column and any of the columns have NULL as its value. To understand it, we consider the example from the table ‘Student_name; which have the following data −mysql> Select * from Student_Name; +---------+-------+---------+ | FName | Mname | Lname ...
Read MoreWhy is it necessary to declare NOT FOUND handler while using MySQL cursor?
We must have to declare NOT FOUND handler while working with MySQL cursor because it handles the situation when cursor could not find any row. It also handles the situation when the cursor reaches the end of the row because every time we call FETCH statement the cursor finds to attempt the next row in the result set. Following is the syntax to declare NOT FOUND handler −DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_name = value;Here var_name is the name of any variable and value would be the value of that variable. For example, we can declare it as ...
Read MoreHow can we retrieve the output having decimal values of a column in a specified format?
MySQL FORMAT() function, converts a number to a format like #, ###, ###.### which is rounded up to the number of decimal places specified and returns the result as a string, can be used to retrieve the output having decimal values of a column in a specified format. To understand it, we are taking an example of table ‘estimated_cost’ which have the following data −mysql> Select * from estimated_cost; +----+-----------------+-----------+---------------+ | Id | Name_Company | Tender_id | Tender_value | +----+-----------------+-----------+---------------+ | 1 | ABC Ltd. | 110 ...
Read MoreHow are actions that take place inside stored procedure and functions replicated?
Actually standard actions carried out in stored procedures and functions are replicated from a master MySQL server to a slave MySQL server. Even the creation of stored procedures and functions carried out through normal DDL statements on a master MySQL server are replicated to a slave MySQL server. In this way, objects will exist on both the servers.The actions that take place inside the stored procedure and functions are replicated because MySQL records each DDL event that occurs inside stored procedures and functions. After recording the events it is replicated to the slave MySQL server. But the actual calls made ...
Read More