[FLINK-32264] Add FIELD support in SQL & Table API

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.18.0
• Fix Version/s: 2.0.0
• Component/s: Table SQL / Planner
• Labels: pull-request-available stale-assigned
• Assignee: Hanyu Zheng
• Reporter: Bonnie Varghese
• Created: 05/Jun/23 18:31
• Updated: 10/Jul/24 06:57

Description

FIELD Function

Description
The FIELD function returns the position of a value in a list of values (val1, val2, val3, ...).

Syntax
The syntax for the FIELD function is:

FIELD( value, ...)
Parameters or Arguments
value
The value to find in the list.
val1, val2, val3, ...
The list of values that is to be searched.
Note
If value is not found in the list of values (val1, val2, val3, ...), the FIELD function will return 0.
If value is NULL, the FIELD function will return 0.
If list of values is NULL, return 0.

Example
Let's look at some  FIELD function examples and explore how to use the FIELD function.

For example:

 

SELECT FIELD('b', 'a', 'b', 'c', 'd', 'e', 'f');
Result: 2
SELECT FIELD('B', 'a', 'b', 'c', 'd', 'e', 'f');
Result: 2
SELECT FIELD(15, 10, 20, 15, 40);
Result: 3
SELECT FIELD('c', 'a', 'b');
Result: 0
SELECT FIELD('g', '');
Result: 0
SELECT FIELD(null, 'a', 'b', 'c');
Result: 0
SELECT FIELD('a', null);
Result: 0

see also:

MySQL:https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_field

Issue Links

• links to: GitHub Pull Request #22849