Oreplace in Teradata

oreplace in teradata

Oreplace in Teradata is used to replace every occurrence of matching string with the new string in Teradata. Oreplace is mainly used to either to replace of remove portions of a string.

Oreplace Syntax:

SELECT oreplace('source_string','string_to_replace','new_string')

Oreplace Example:

SELECT oreplace('TeradataPoint','Point','Tutorial');

Output:

TeradataTutorial

Also Read – Concatenation Operator || in Teradata

Replacing a string in a column using oreplace in Teradata

In a particular column, you can replace a certain string or characters using oreplace function. 

Syntax:

SELECT oreplace(column_name, 'string_to_replace','new_string');

Example:

Consider a column of a table is like this.

Pincode
Pincode- 700001
Pincode- 400614
Pincode- 110001
Pincode- 440012

You can remove “Pincode-” string using below sql.

SELECT oreplace(Pincode,'Pincode-','');

Output: (Replacing “Pincode-” using empty string)

Pincode
700001
400614
110001
440012