There will be a significant change in the output if we have the combination of NULL and other values in the list of strings, provided as arguments in FIELD() function. Following example will demonstrate it Example mysql> Select FIELD('good', 'Ram', 'is', 'good', 'boy'); +---------------------------------------+ | FIELD('good', 'Ram', 'is', 'good', 'boy') | +---------------------------------------+ | 3 | +---------------------------------------+ 1 row in set (0.00 sec) ... Read More

If strict SQL mode is disabled and we insert an invalid value (which is not in the list of permitted enumeration values) into ENUM, MySQL will insert an empty string instead of throwing an error. If strict SQL mode is enabled, MySQL throws an error when inserting invalid value. Invalid values without strict SQL mode Strict SQL mode is disabled by default. When it is disabled, if we enter an invalid value that is not in the ENUM list, it returns an empty string. Let us understand this by using the example below. Example In the below example we have ... Read More

In MySQL, the ENUM data type allows you to create a column with specified values like 'A', 'B', 'C', and 'D'. This feature is useful for maintaining data consistency, as it restricts entry to a specific set of values. However, the behavior of ENUM columns can vary depending on whether the NOT NULL constraints are applied and also depends on SQL modes. How to handle ENUM values with SQL modes 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 throw an error. ... 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)

In MySQL, the ENUM data type enables you to define a column using only a collection of predetermined values. Each value in the ENUM list is assigned a position number known as an index (which begins with 1). These index numbers represent the positions of the values in the list, not the actual data. For example, if the ENUM list is ('Male', 'Female', 'Other'), Male has an index of 1 while Female has an index of 2 and Other will have an index of 3. This further enables MySQL to store and compare the values more effectively, though actual ... 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