How to use Netezza Replace Function and Examples

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:Netezza
  • Reading time:2 mins read

By default, there is no Netezza replace function. Replace function is very much needed in case if you are manipulating strings and there is a need to replace the particular value when displaying reports. Netezza provides replace function in the Netezza SQL extensions toolkit that administrator has to install and grant permission to use.

Netezza Replace Function

The Netezza replace() function replaces each instance of a pattern in the input with the value in the string replacement.

Syntax:

The replace() function has the following syntax:

REPLACE(varchar input, varchar pattern, varchar replacement);

The input value specifies the varchar or nvarchar value which may be column or expression and contains the value that needs to be replaced. The pattern value specifies the characters to replace. The replacement value specifies the characters to substitute for each instance of the pattern value.

Netezza Replace Function Examples

Below are the some of the Netezza replace function examples:

Replace the name ‘ABC’ with string ‘AAA’ for name column from patient table.

TRAINING.ADMIN(ADMIN)=> select name, SYSTEM..REPLACE(name,'ABC','AAA') as New_Name from patient_1 where ID = 1; 
 NAME | NEW_NAME 
------+---------- 
 ABC | AAA 
(1 row)

Replace ‘Netezza’ with ‘IBM Netezza’ in the string.

TRAINING.ADMIN(ADMIN)=> select system..replace('This is Netezza replace function','Netezza','IBM Netezza'); 
 REPLACE 
-------------------------------------- 
 This is IBM Netezza replace function 
(1 row)

Read:

Tags: , ,