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
Database Articles
Page 271 of 547
How does the precedence of || operator depend on PIPES_AS_CONCAT SQL mode?
As we know that in MySQL by default || operator is a logical OR operator but it depends upon PIPES_AS_CONCAT SQL mode. If PIPES_AS_CONCAT SQL mode is enabled, then || operator works as string concatenation. At that time its precedence would be between ^ and the unary operator. Following example will make it understand −mysql> Set @C='tutorials'; Query OK, 0 rows affected (0.00 sec) mysql> Set @D='point'; Query OK, 0 rows affected (0.00 sec) mysql> Select @C||@D; +--------+ | @C||@D | +--------+ | 1 | +--------+ 1 row in set (0.00 sec)The result set of the ...
Read MoreIn MySQL, how does the precedence of ! operator in comparison with NOT operator depends upon HIGH_NOT_PRECEDENCE SQL mode?
In MySQL, basically the precedence of ! operator in comparison with NOT operator depends upon the enabling or disabling of HIGH_NOT_PRECEDENCE SQL mode as follows −Disabled HIGH_NOT_PRECEDENCE SQL − In this case,! the operator has higher precedence than NOT operator.Enabled HIGH_NOT_PRECEDENCE SQL − In this case,! the operator has the same precedence as NOT operator.
Read MoreHow can we combine values of two or more columns of MySQL table and get that value in a single column?
For combining values of two or more columns, we can use MySQL CONCAT() function. In this case, the arguments of the CONCAT() functions would be the name of the columns. For example, suppose we have a table named ‘Student’ and we want the name and address of the student collectively in one column then the following query can be written −mysql> Select Id, Name, Address, CONCAT(ID, ', ', Name, ', ', Address)AS 'ID, Name, Address' from Student; +------+---------+---------+--------------------+ | Id | Name | Address | ID, Name, Address | +------+---------+---------+--------------------+ | 1 ...
Read MoreWhat is MySQL NULL-safe equal operator and how it is different from comparison operator?
MySQL NULL-safe equal operator, equivalent to standard SQL IS NOT DISTINCT FROM operator, performs an equality comparison like = operator. Its symbol is . It performs differently from the comparison operators in the case when we have NULL as both the operands. Consider the following examples to understand NULL-safe operator along with its difference with comparison operator −mysql> Select 50 50, NULL NULL, 100 NULL; +-----------+---------------+--------------+ | 50 50 | NULL NULL | 100 NULL | +-----------+---------------+--------------+ | 1 | 1 | 0 | +-----------+---------------+--------------+ 1 row in set (0.00 sec) mysql> Select 50 = 50, NULL = NULL, 100 = NULL; +---------+-------------+------------+ | 50 = 50 | NULL = NULL | 100 = NULL | +---------+-------------+------------+ | 1 | NULL | NULL | +---------+-------------+------------+ 1 row in set (0.00 sec)
Read MoreWhat happens if we provide NULL as an argument to MySQL CHAR() function?
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.
Read MoreWhat MySQL returns if we provide value larger than 255 as argument to MySQL CHAR() function?
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).
Read MoreHow can we produce a string, other than default binary string, in a given character set by MySQL CHAR() function?
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 MoreIn function INSERT(str, Pos, len, newstr), what would be the result if 'Pos' is not within the length of the string?
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 MoreIn MySQL, how can we insert a substring at the specified position in a string?
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 MoreHow can I create a MySQL stored procedure that returns multiple values from a MySQL table?
We can create a stored procedure with both IN and OUT parameters to get multiple values from a 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 ...
Read More