Netezza Appliance does not support the Netezza array functions without Netezza SQL Extension tool kit. You have to install the tool kit explicitly on the required data type.
Read:
- Guide to Download and install Netezza SQL Extension Tool kit
- Netezza Data Types and Restrictions
- Netezza String Functions and Examples
- IBM Netezza Extract Function and its Usage
- Different types of Netezza Trim Functions and Examples
These Netezza Array functions available in the toolkit rely on the Netezza array data type. IBM Netezza does not support the user defined data types, array type in Netezza is stored in varchar fields.
Netezza Array Functions
Netezza array() function
The array() function creates an array of a specified type.
Syntax:
array(int type);
The type value is an integer code that specifies the type of array to create:
| Code | Type |
| 1 | int1 |
| 2 | int2 |
| 3 | int4 |
| 4 | int8 |
| 5 | date |
| 6 | time |
| 7 | timestamp |
| 8 | varchar |
| 9 | nvarchar |
| 10 | float |
| 11 | double |
| 15 | timez |
Netezza Array Examples
TRAINING.ADMIN(ADMIN)=> create table array_t(col1 int,col2 varchar(100)); CREATE TABLE TRAINING.ADMIN(ADMIN)=> insert into array_t values(1,system..array(2)); INSERT 0 1 TRAINING.ADMIN(ADMIN)=> select * from array_t; COL1 | COL2 ------+------ 1 | ARR (1 row) TRAINING.ADMIN(ADMIN)=>
Netezza Array add_element() function
The add_element() function appends an array element to the end of an array.
Syntax:
add_element(array input, value);
Netezza Array add_element() Examples
TRAINING.ADMIN(ADMIN)=> update array_t set col2 = SYSTEM..add_element(col2,1); UPDATE 1 TRAINING.ADMIN(ADMIN)=> select * from array_t; COL1 | COL2 ------+------ 1 | ARR (1 row)
Netezza array_combine() and narray_combine() functions
The array_combine() and narray_combine() functions combine the elements in an array into a single delimited varchar or nvarchar value.
Syntax:
array_combine(array input, char delimiter);narray_combine(array input, nchar delimiter);
The input value specifies the array to decompose into a single varchar or nvarchar value. The delimiter value specifies the delimiter that distinguishes the array elements.
Netezza array_combine() Function Example
TRAINING.ADMIN(ADMIN)=> select SYSTEM..array_combine(col2,'~') from array_t; ARRAY_COMBINE --------------- 1~10~113~16 (1 row)
Netezza array_concat() Function
The array_concat() function concatenates two arrays, creating an array that contains all the elements in the first array followed by all the elements in the second array.
Syntax:
array_concat(array array1, array array2);
Netezza array_concat() Example
TRAINING.ADMIN(ADMIN)=> select (SYSTEM..array_concat (SYSTEM..array(2),SYSTEM..array(2))); ARRAY_CONCAT -------------- ARR (1 row)
Netezza array_count() function
The array_count() function returns the number of elements in an array.
Syntax:
array_count(array input);
Netezza array_count() Examples
TRAINING.ADMIN(ADMIN)=> select SYSTEM..array_count(col2)from array_t; ARRAY_COUNT ------------- 4 (1 row)
Netezza array_split() function
The array_split() function parses the input for elements, which are separated by a delimiter, to create an array.
Syntax:
array_split(varchar input, varchar delimiter [, [int type]);
Read: Netezza Split Delimited Fields into Table Records and Examples
Netezza array_type() function
The array_type() function returns the type of an array.
Syntax:
array_type(array input);
Netezza array_type() Function Example
TRAINING.ADMIN(ADMIN)=> select SYSTEM..array_type(col2) from array_t; ARRAY_TYPE ------------ 2 (1 row)
2 is the integer array type.
Netezza delete_element() function
The delete_element() function deletes an element from an array.
Syntax:
delete_element(array input, int|vharchar|nvharchar index);
Netezza delete_element() Example
TRAINING.ADMIN(ADMIN)=> select SYSTEM..delete_element(col2,1)from array_t; DELETE_ELEMENT ---------------- ARR (1 row)
Netezza element_name() Function
The element_name() and nelement_name() functions return the name of an element if it exists.
Syntax:
element_name(array input, int index);
Netezza element_name() Example
TRAINING.ADMIN(ADMIN)=> select SYSTEM..element_name(SYSTEM..add_element(SYSTEM..array(4),4,'dwgeek.com'),1); ELEMENT_NAME -------------- dwgeek.com (1 row)
Netezza get_value_type() Function
The get_value_type() function retrieves the value that is stored in an array element.
Syntax:
get_value_{int|varchar|double|time|timestamp} (array input, int|varchar index);
Netezza get_value_type() Example
TRAINING.ADMIN(ADMIN)=> select SYSTEM..get_value_int(col2,1)from array_t; GET_VALUE_INT --------------- 1 (1 row)
Netezza replace_element() Function
The replace_element() function replaces an element in an array.
Syntax:
replace_element(array input, int index, varchar value)
Netezza replace_element() Example
TRAINING.ADMIN(ADMIN)=> select SYSTEM..replace_element(col2,1,15)from array_t; REPLACE_ELEMENT ----------------- ARR (1 row)