Netezza Array Functions and Examples

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:Netezza
  • Reading time:6 mins read

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.

netezza array functions

Read:

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)