String Functions 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

http://netezzamigration.blogspot.com/2014/09/sql-server-to-netezza-migration-part-2.html

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