Trimming the array (TRIM_ARRAY function)

Trimming an array is a task that you would perform using the TRIM_ARRAY function when you want to remove unnecessary array elements from the end of an array.

Before you begin

  • Read: Array data types
  • Read: Restrictions on array data types
  • Privileges required to execute the SET statement

About this task

You would perform this task within SQL PL code in order to quickly remove array elements from the end of an array.

Procedure

  1. Define a SET statement:
    1. Declare and specify an array variable that is of the same type as the array to be modified, or re-use the same array variable.
    2. Specify the assignment symbol, '='.
    3. Specify the name of the TRIM_ARRAY function and within the required brackets, the name of the array and the number of elements to be trimmed.
  2. Execute the SET statement.

Results

If the SET statement executes successfully, the array phones should contain the updated value.

Example

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: 
SET phones = TRIM_ARRAY ( phones,  2 );
The array, phones, will be defined as:
phones		index		0							1		 				
         phone		'416-223-2233'	'416-933-9333'

What to do next

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.