Netezza Split Delimited Fields into Table Records and Examples

If you are working on the large volume of different source system then you may come across the requirement of Netezza split delimited fields into table records.

There are some methods those you can use as per your requirements:

Netezza Split Delimited Fields using ARRAY_SPLIT function

You can use the ARRAY_SPLIT function that strips off record delimiter. The Netezza array functions are available in Netezza SQL extension tool kit that you have to install explicitly on required database.

Read:

• Netezza String Function and its Usage with Examples
• Netezza Array Functions and Examples
• Download and install Netezza SQL Extension tool kit
• Netezza nzsql command its usage and Examples
• IBM Netezza Extract Functions and Examples
• IBM Netezza Pivot Row to Column with Example

Netezza Split Delimited Fields using array_split Example

select SYSTEM..GET_VALUE_VARCHAR(SYSTEM..ARRAY_SPLIT(tab.str, '~'),1)
from (select 'abc~bcd~netezza~ibm' as str ) tab

TRAINING.ADMIN(ADMIN)=> select * from demo_str_split; 
 COL1 | COL2 | COL3 | COL4 
------+------+------+------ 
(0 rows)

TRAINING.ADMIN(ADMIN)=> insert into demo_str_split select TRAINING.GET_VALUE_VARCHAR(SYSTEM..ARRAY_SPLIT(tab.str,'~'), 1), SYSTEM..GET_VALUE_VARCHAR(SYSTEM..ARRAY_SPLIT(tab.str,'~'), 2), SYSTEM..GET_VALUE_VARCHAR(SYSTEM..ARRAY_SPLIT(tab.str,'~'), 3), SYSTEM..GET_VALUE_VARCHAR(SYSTEM..ARRAY_SPLIT(tab.str,'~'), 4) from (select 'abc~bcd~netezza~ibm' as str ) tab; 
INSERT 0 1

TRAINING.ADMIN(ADMIN)=> select * from demo_str_split; 
COL1 | COL2 | COL3 | COL4 
------+------+---------+------ 
 abc | bcd | netezza | ibm 
(1 row)
TRAINING.ADMIN(ADMIN)=>

Netezza Split Delimited Fields using regexp_extract_all function

You can also use the Netezza regular expression functions that are present in the SQL extension tool kit.

Netezza Split Delimited Fields regexp_extract_all Examples

select SYSTEM..GET_VALUE_VARCHAR(SYSTEM..regexp_extract_all(tab.str, '.*?~'),1)
from (select 'abc~bcd~netezza~ibm' as str ) tab

Other Methods that be used to split the delimited records

You can also use the substring and strpos functions to perform the same task as the Netezza array functions does. You can also write the generic stored procedure that accept the parameter and perform the string split and load into tables.

Read:

• Working with Netezza Stored Procedure and Working Examples
• Execute Dynamic SQL in Netezza and Examples
• Netezza User Defined Functions and Sample Examples
• Commonly Used Netezza Basic Commands

Feel free to comment any other methods that can be used to Split Delimited Fields into Table Records.