If strict SQL mode is disabled and we insert invalid value (which is not in the list of permitted enumeration values) into ENUM then MySQL will insert an empty string instead of throwing an error. But if strict SQL mode is enabled then MySQL throws an error on inserting invalid value.ExampleAfter disabling the strict SQL mode, we insert the invalid string into ENUM as follows −mysql> Insert into result(id, name, grade) values(100, 'Gaurav', 'abcd'); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> Select * from result; +-----+--------+-------+ | Id | Name    | Grade | +-----+--------+-------+ | ... Read More

MySQL accepts empty values for enumeration only if SQL mode is not set as TRADITIONAL, STRICT_TRANS_TABLES or STRICT_ALL_TABLES. Otherwise, MySQL would not accept empty values and throws an error. As we know that each enumeration value is having an index value, the empty value would have 0 index value.Examplemysql> SET SQL_MODE ='Traditional'; Query OK, 0 rows affected (0.00 sec) mysql> Insert into result(id, name, grade) values(100, 'Raman', ''); ERROR 1265 (01000): Data truncated for column 'Grade' at row 1Now, after changing SQL mode we would be able to insert empty string as follows −mysql> Set SQL_MODE =''; Query OK, ... Read More

In case if all the arguments (list of strings) of FIELD() function are NULL then MySQL will return 0 as output.Examplemysql> Select FIELD('Ram',NULL,NULL,NULL,NULL); +----------------------------------+ | FIELD('Ram',NULL,NULL,NULL,NULL) | +----------------------------------+ |                               0  | +----------------------------------+ 1 row in set (0.00 sec)

As we know that NULL fails equality comparison with any value hence if the search string, provided in FIELD() function, is NULL then MySQL returns 0 as output.Examplemysql> Select FIELD(NULL,'Ram','is','good','boy'); +-------------------------------------+ | FIELD(NULL,'Ram','is','good','boy') | +-------------------------------------+ |                                   0 | +-------------------------------------+ 1 row in set (0.00 sec)

Suppose if the search string is not in the list of strings provided as the arguments in FIELD() function then MySQL will return 0 as output.Examplemysql> Select FIELD('Ram','New','Delhi'); +----------------------------+ | FIELD('Ram','New','Delhi') | +----------------------------+ |                         0  | +----------------------------+ 1 row in set (0.00 sec)

We can use FIELD() function to find the index position of a particular string from a list of strings.SyntaxFIELD(str search,String1, String2,…StringN)Here, the str search is the string whose index number we want to search and String1, String …StringN is the list of strings from which the search would happen.Examplemysql> Select FIELD('good', 'Ram', 'is', 'a', 'good', 'boy')AS 'Index Number of good'; +----------------------+ | Index Number of good | +----------------------+ |                  4   | +----------------------+ 1 row in set (0.00 sec)

Actually, the elements listed in the ENUM column specifications are assigned index numbers which begin with 1. Here the term “index” is indicating towards the position within the list of enumeration values and they are not related to table indexes. With the help of following examples we can show that each MySQL enumeration has an index value −By inserting index number instead of enumeration valueWe can insert the values in ENUM column with the help of their index numbers rather than writing the values. For example, in the table below we have two ENUM values ‘pass’ and ‘fail’. As ‘pass’ ... Read More

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)

NULL as both argumentsMySQL returns blank output if we will use NULL as both of the arguments in CONCAT_WS() function.Examplemysql> Select CONCAT_WS('', NULL, NULL); +-------------------------+ | CONCAT_WS('', NULL, NULL) | +-------------------------+ |                         | +-------------------------+ 1 row in set (0.00 sec)NULL as one of the argumentMySQL returns the value of the other argument as output if we will use NULL as one of the argument in CONCAT_WS() function.Examplemysql> Select CONCAT_WS('', NULL, 'Delhi'); +----------------------------+ | CONCAT_WS('', NULL, 'Delhi') | +----------------------------+ | Delhi                 ... Read More

Both CONCAT() and CONCAT_WS() functions are used to concatenate two or more strings but the basic difference between them is that CONCAT_WS() function can do the concatenation along with a separator between strings, whereas in CONCAT() function there is no concept of the separator. Other significance difference between them is that CONCAT()function returns NULL if any of the argument is NULL, whereas CONCAT_WS() function returns NULL if the separator is NULL.ExampleThe example below demonstrate the difference between CONCAT() and CONCAT_WS() function −mysql> Select CONCAT('Ram', 'is', 'a', 'good', 'student') AS 'Example of CONCAT()'; +---------------------+ | Example of CONCAT() | +---------------------+ ... Read More