FIND_IN_SET() function returns NULL as output if any of the argument i.e. either search string or string list, is NULL. Of course, It will also return NULL if both of the arguments are NULL.Examplemysql> Select FIND_IN_SET(NULL,'Ram is a good boy') AS Result; +--------+ | Result | +--------+ | NULL   | +--------+ 1 row in set (0.00 sec) mysql> SELECT FIND_IN_SET('RAM',NULL)AS RESULT; +--------+ | RESULT | +--------+ | NULL   | +--------+ 1 row in set (0.00 sec) mysql> SELECT FIND_IN_SET(NULL,NULL); +------------------------+ | FIND_IN_SET(NULL,NULL) | +------------------------+ |                   NULL | +------------------------+ 1 row in set (0.00 sec)

When we use FIND_IN_SET() function in WHERE clause then it searches the search string within the given string as specified in the argument and retrieves all the columns from concerned rows. Following is an example to demonstrate it −ExampleIn this example, we are getting the columns from ‘Student’ table where the rows have the value of name as ‘Gaurav’. Here the FIND_IN_SET() function will search the search string ‘Gaurav’ from the values of column ‘Name’.mysql> Select Id, Name, Address, Subject from student WHERE FIND_IN_SET('Gaurav', Name); +------+--------+---------+-----------+ | Id   | Name   | Address | Subject   | +------+--------+---------+-----------+ ... Read More

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