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_’
 
Result:- IBM_NETEZZA
 
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 ‘-‘
 
IBM-NETEZZA-DATABASE
 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’ 
 
Result:- NETEZZAIBM
 
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.
 
SELECT ‘NETEZZA’||NULL
 
result:- null 
 
SELECT ‘NETEZZA’||COALESCE(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

substr(‘VAMSHI’,LENGTH(‘VAMSHI’)-1)
RESULT:- HI
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
 
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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