Determining if an array element exists

Determining if an array element exists and has a value is a task that can be done using the ARRAY_EXISTS function.

Before you begin

Read: Ordinary array data type
Read: Restrictions on the array data type
Privileges required to execute the IF statement or any SQL statement in which the ARRAY_EXISTS function is referenced.

You would perform this task within SQL PL code in order to determine if an array element exists within an array.

Define an IF statement:
1. Define a condition that includes the ARRAY_EXISTS function.
2. Specify the THEN clause and include any logic that you want to have performed if the condition is true and add any ELSE caluse values you want.
3. Close the IF statement with the END IF clause.
Execute the IF statement.

For an array of phone numbers defined as:

phones		index		0		           1		           2		           3 				
         phone		'416-223-2233'	'416-933-9333'	'416-887-8887'	'416-722-7227'

After executing the following, the variable x will be set to 1.

IF (ARRAY_EXISTS(phones, 2)) THEN
  SET x = 1;
END IF;

If the SET statement failed to execute successfully:

Verify the SQL statement syntax of the SET statement and execute the statement again.
Verify that the local variable is of the correct data type.
Verify that the array was created successfully and currently exists.