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 123 of 355
How can we use the MySQL reserved words as an identifier?
We must have to use quotes with reserved words to use them as an identifier. The quotes can be single or double depends upon ANSI_QUOTES SQL mode.If this mode is disabled then the identifier quote character is the backtick (“`”). Consider the following example in which we created a table named ‘select’ −mysql> create table `select`(id int); Query OK, 0 rows affected (0.19 sec)If this mode is enabled then we can use backtick (“`”) and double quotes (“”) both as identifier quote character. Consider the following example in which we created a table named ‘trigger’ −mysql> Create table "trigger" (id ...
Read MoreWhat happens if the substring is there for more than one time in the string given as the arguments of LOCATE() function?
In case if the substring is there for more than one time in the string then MySQL LOCATE() function will return the position of the first occurrence of the substring.Examplemysql> Select LOCATE('good','Ram is a good boy. Is Ram a good boy?')As Result; +--------+ | Result | +--------+ | 10 | +--------+ 1 row in set (0.00 sec)As we can see that the substring ‘good’ is in the string for two times. The first occurrence is at position 10 and another occurrence is at position 29. MySQL returns the position of the first occurrence.
Read MoreHow can I manage the start position of searching in MySQL LOCATE() function?
As we know that by default searching in LOCATE() function starts from beginning. We can manage the start position by giving an argument to specify the position from which we want to start the search in string. Following example will demonstrate it −Examplemysql> Select LOCATE('good','Ram is a good boy. Is Ram a good boy?',11)As Result; +--------+ | Result | +--------+ | 29 | +--------+ 1 row in set (0.00 sec)In the above example, we have given the value 11 as the argument for position. It means that MySQL will start searching from 11th position.
Read MoreWhat happens if I will use integer values as arguments of MySQL LOCATE() function?
MySQL allows us to use integer values as the arguments of the LOCATE() function. We do not need to use quotes. It can be demonstrated with the help of the following example −Examplemysql> Select LOCATE(5,1698235); +-------------------+ | LOCATE(5,1698235) | +-------------------+ | 7 | +-------------------+ 1 row in set (0.00 sec) mysql> Select LOCATE(56,1698235); +--------------------+ | LOCATE(56,1698235) | +--------------------+ | 0 | +--------------------+ 1 row in set (0.00 sec) mysql> Select LOCATE(23,1698235); +--------------------+ | LOCATE(23,1698235) | +--------------------+ | 5 | +--------------------+ 1 row in set (0.00 sec)
Read MoreHow an enumeration value in MySQL can be used in an expression?
As we know that enumeration values are associated with index values hence if we will use enumeration values in an expression then all the calculations would be done on index numbers. The following example will clarify it −mysql> Select * from Result; +-----+--------+-------+ | Id | Name | Grade | +-----+--------+-------+ | 100 | Gaurav | GOOD | | 101 | Rahul | POOR | | 102 | Rahul | NULL | | 103 | Mohan | | +-----+--------+-------+ 4 rows in set (0.00 sec) mysql> Select SUM(Grade) from result; +------------+ | SUM(Grade) | +------------+ ...
Read MoreHow can CONCAT_WS() function be used with MySQL WHERE clause?
When we use CONCAT_WS() function with WHERE clause then the output would be based upon the condition provided in WHERE clause. It can be understood from the example of ‘Student’ table as followsExamplemysql> Select CONCAT_WS(' ',Name, Last_name, 'Resident of', Address, 'is studying', Subject)AS 'Student Detail' from student WHERE id = 20; +----------------------------------------------------------------+ | Student Detail | +----------------------------------------------------------------+ | Gaurav Rathore Resident of Jaipur is studying Computers | +----------------------------------------------------------------+ 1 row in set (0.00 sec)
Read MoreHow can CONCAT() function be applied on columns of MySQL table?
We can use CONCAT() function to combine the values of two or more columns. 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 | Gaurav | Delhi ...
Read MoreHow can CONCAT() function be used with MySQL WHERE clause?
Suppose from the table ‘Student’ we want to concatenate the values of columns, ‘Name’, ‘Address’ and ‘Columns’, based on the condition that is also a concatenation of values from columns, ’Name’, ‘Subject’, provided in WHERE clause with the help of CONCAT() function. We can use the following query to give the output −mysql> Select CONCAT(Name, ' ', 'Resident of', ' ', Address, ' ', 'is', ' ', 'Studying', ' ', Subject)AS 'Detail of Student' from Student WHERE CONCAT(Name, Subject) = "AaravHistory"; +----------------------------------------------+ | Detail of Student | ...
Read MoreHow Can MySQL CAST handle overflow?
MySQL CAST can handle overflow occurs during numerical expression assessment. Suppose if numeric expression evaluation produces overflow then MySQL reflects an error message. Now to handle this overflow we can change that numeric value to UNSIGNED with the help of CAST.For example on adding 1 to BIGINT maximum value, MySQL produce an error due to overflow as follows −mysql> Select 9223372036854775807 + 1; ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807+1)'Now, with the help of CAST, MySQL handles this kind of overflow as follows:mysql> Select CAST(9223372036854775807 AS UNSIGNED) +1; +------------------------------------------+ | CAST(9223372036854775807 AS UNSIGNED) +1 | +------------------------------------------+ | 9223372036854775808 | +------------------------------------------+ 1 row in set (0.07 sec)
Read MoreWhat is the use of NO_UNSIGNED_SUBTRACT SQL mode in handling overflow?
In case of enabled SQL strict mode, subtraction between integers value in which one is of UNSIGNED type will produce an unsigned result by default. But MySQL produces an error if the result is a negative one. It can be observed with the following example −mysql> SET sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql> Select CAST(0 AS UNSIGNED) -1; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'The error after the query above shows that it is an overflow after numeric arithmetic expression.Now, it can be handled with ...
Read More