String Functions in Netezza

Posted: September 22, 2014 in Netezza Migration

Netezza Array Functions and their advantages.

How to replace a character in the string in Netezza.

Ex:- SELECT array_combine(Array_split(‘1NETEZZA’,‘1’),‘IBM_’);
The above query replace 1 with ‘IBM_’


The above function can be used to replace anything (can be used for multiple replace)

Ex:- SELECT array_combine(Array_split(‘129|259|659|2558|6589’,‘|’),‘&’);

Result:- 129&259&659&2558&6589

How to split a string or Stringtokenizer in Netezza

SELECT Get_value_varchar(Array_split(‘IBM-NETEZZA-DATABASE’, ‘-‘), 3); 

Result:- DATABASE 

In the above example we want to get database which is the 3rd character to ‘-‘

 1    2       3

In the same example if we want to get NETEZZA place 2 in the place of 3. 

Netezza string concatenation

Select ‘NETEZZA’||’IBM’ 


Points to keep in mind while doing string concatenation.
1) check if the columns are null-able 

if yes then one must use coalesce function to handle null values. Lets see if not what will happen.


result:- null 


Result:- NETEZZA

netezza string replace

Select REPLACE(‘pageup’,’up’,down’);

Result:- pagedown

How to replace a specific character any where in the string any number of times.please refer to the below link

netezza substring function

Formula for last 2 alphabets do Length -1
For 3 its length -2
For 4 its Length -3
Use when we need Right hand side digits or charade


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s