? FIELD() Function in SQL

The FIELD() function in SQL returns the index (position) of a value within a list of values.

It is mainly supported in MySQL.

? Syntax:

FIELD(search_value, val1, val2, val3, ...)

• search_value: The value to search for.

• val1, val2, ...: The list of values to search in.

? Behavior:

• If search_value is found, it returns its 1-based index.

• If not found, it returns 0.

? Example:

SELECT FIELD('Banana', 'Apple', 'Banana', 'Cherry');

? Output:

2

SELECT FIELD('Mango', 'Apple', 'Banana', 'Cherry');

? Output:

0  -- 'Mango' not found

? Use Case Example:

Sort rows based on a custom order:

SELECT name, statusFROM ordersORDER BY FIELD(status, 'Pending', 'Processing', 'Completed');

This will order the rows according to the custom status sequence.

? Compare with ELT():

Let me know if you want to use FIELD() in WHERE, ORDER BY, or with dynamic data.