String Functions in Netezza

Posted: September 21, 2014 in Netezza

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