Netezza: Split delimited string how to

Split delimited string using Netezza SQL Extensions Toolkit

Also see post how to JOIN or concatenate string:  Netezza: concatenate rows into string with XMLAGG()

---

Alternative 1: 

Using ARRAY_SPLIT function that strips off a delimiter. 

select SQL_EXTENSIONS..ARRAY_COMBINE(

              SQL_EXTENSIONS..ARRAY_SPLIT( '1,2,3,,,4,5,abc,mama,al123num,',',' )

          , '|'

);

---

Alternative 2:

Using regexp_extract_all function that preserves the delimiter. 

select SQL_EXTENSIONS..array_combine(

            SQL_EXTENSIONS..regexp_extract_all( '1,2,3,,,4,5,abc,mama,al123num,', '.*?,')

                      , '|'

);

Refer to IBM Netezza SQL Extensions Toolkit 7.2 documentation.

Netezza: datatype storage CHARACTER v.s. NATIONAL CHARACTER

SUMMARY: 

There is NO significant storage saving (just 3.7%) when choosing between  NATIONAL CHARACTER (Unicode) and CHARACTER (ASCII) for database table design at Netezza.

I recommend choosing a datatype that pertains to design and scalability of the application.

DETAILS: 

Compare storage saving using CHARACTER VARYING  instead of NATIONAL CHARACTER VARYING  datatype at Netezza NPS 7.2.1.

NATIONAL CHARACTER	CHARACTER
CREATE TABLE ARCHIVE_TABLE_201405 (        CMTS_NM     NATIONAL CHARACTER VARYING (30 ),        INTF_NM     NATIONAL CHARACTER VARYING (30 ),        MAC_ADDR    NATIONAL CHARACTER VARYING (20 ),        CM_STA      NATIONAL CHARACTER VARYING (20 ),        TM_OFST     CHARACTER VARYING (20 ),        IP_ADDR     NATIONAL CHARACTER VARYING (20 ),        NODE_KEY           NATIONAL CHARACTER VARYING (50 ),        BATCH_TS    TIMESTAMP,        AS_OF_DT    TIMESTAMP,        DATA_ACQ_ID INTEGER ) DISTRIBUTE ON (AS_OF_DT, MAC_ADDR);	CREATE TABLE ARCHIVE_TABLE_201405_VARCHAR (        CMTS_NM     CHARACTER VARYING (30 ),        INTF_NM     CHARACTER VARYING (30 ),        MAC_ADDR    CHARACTER VARYING (20 ),        CM_STA      CHARACTER VARYING (20 ),        TM_OFST     CHARACTER VARYING (20 ),        IP_ADDR     CHARACTER VARYING (20 ),        NODE_KEY           CHARACTER VARYING (50 ),        BATCH_TS    TIMESTAMP,        AS_OF_DT    TIMESTAMP,        DATA_ACQ_ID INTEGER ) DISTRIBUTE ON (AS_OF_DT, MAC_ADDR);
ALLOCATED_GB: 38 Gb USED_GB     : 38 Gb RECORDS     : 1,369,391,278	ALLOCATED_GB: 36.7 Gb USED_GB     : 36.7 Gb RECORDS     : 1,369,391,278

From description below, once could assume that each NVARCHAR (Unicode) character takes 4x larger storage than VARCHAR (ASCII).  The result of the experiment indicates that this is not the case.

VARCHAR	CHARACTER VARYING, VARCHAR(n), CHARACTER VARYING(n), CHAR VARYING(n)	Variable length character string to a maximum length of n. If you do not specify n, the default is an unsized VARCHAR value. There is no blank padding, and the value is stored as entered. The maximum character string size is 64,000.
NVARCHAR	NATIONAL CHARACTER VARYING, NATIONAL CHAR VARYING, NATIONAL CHAR VARYING(n), NATIONAL CHARACTER VARYING(n), and NVARCHAR(n)	Variable length character string to a maximum length of n. If you do not specify n, the default is an unsized NVARCHAR value. The maximum length is 16,000 characters.

REF: Netezza Data types and aliases

Netezza: ALTER TABLE to MODIFY a column datatype

At Netezza, you may NOT change datatype of a column with ALTER TABLE xxxx MODIFY COLUMN COL_1:

Allowed only to:

• Change a column name
• Change a column default value
• Only increase length of a VARCHAR column (not to decrease).

---

--- FAIL: Changing DATE to TIMESTAMP

/*ERROR [HY000] ERROR:  ALTER TABLE: only varchar fields can be modified */

alter table XXX MODIFY COLUMN COL TIMESTAMP;

--- FAIL: Changing VARCHAR(5) to VARCHAR(4)

/*ERROR [HY000] ERROR:  ALTER TABLE: varchar field size can only be increased */

alter table XXX MODIFY COLUMN COL3 VARCHAR(4 );

--- SUCCESS: Changing VARCHAR(3) to VARCHAR(5)

/* Start time 3/28/2014 11:25:00 AM, end time 3/28/2014 11:25:00 AM. */

/* Duration 0.0624004 sec. */

alter table XXX MODIFY COLUMN COL3 VARCHAR(5 );

Refer:

• ALTER TABLE
  Use the ALTER TABLE command to change the structure of an existing table. If the table is in use by an active query, the ALTER command waits until that query completes.