Field

MySQL Statement: Field
Categories:
Tags:

FIELD(<compare_field or value>, <order_1_value>, <order_2_value>, <order_3_value>, …)

Works in: From MySQL 4.0

FIELD() is a function that returns the index position of a comma-delimited list if the value you are searching for exists.

Specific value

-- Return 1
SELECT FIELD('A', 'A', 'B', 'C');

-- Return 2
SELECT FIELD('B', 'A', 'B', 'C');

-- Return 3
SELECT FIELD('C', 'A', 'B', 'C');

-- Return 0
SELECT FIELD('D', 'A', 'B', 'C');

-- Return 0
SELECT FIELD('OtherValue', 'A', 'B', 'C');

Specific column

SELECT FIELD(status, 'A', 'B', 'C');

Field function order by

SELECT *
FROM mytable
WHERE status IN ('A', 'B', 'C', 'D')
ORDER BY FIELD(id, 'C', 'B', 'A', 'D') DESC;

Field setting

The other value that does not set in the FIELD function will all return 0

FIELD Setting C B A D Other Value
Index 1 2 3 4 0

The ORDER BY values are evaluated by what FIELD() returns

Order by result

Field Index status
4 D
3 A
2 B
1 C
0 Other1
0 Other2

Reference