Updated March 4, 2023
Introduction to Teradata REPLACE
String level replace is among the most common operations in string processing. Being a very common operation, every sequential database will have these replace level operations available in it. In Teradata, this string level replace process is achieved by means of OREPLACE function. This OREPLACE function has the capability of replacing some specific characters, or one specific character in a string with an expected value. Here the source string, search string, and the replace string values are the key items in the OREPLACE function. The functionally is discussed from the syntax perspective below. The more import characteristic of the replace function is that all the instances of the string which is expected to be replaced will be replaced in the given source string.
Syntax:
OREPLACE (Source_string, Search_string, Replace_string)| Syntax Element | Description |
| Source_string | The Source string is the string value in which the search operation is performed. Here the syntax will have the source string item as the first item in the syntax of the OREPLACE function. here the needed sting to be searched and replaced will be searched and the replace operation will be performed upon. The source string when is of NULL value then the output value returned will also be NULL. |
| Search_String | The Search string is the string which is expected to be searched in the corresponding source string. This search string will be the value responsible for search and replace operation. So, the value of the search string will be searched upon the source string and then the replace string will be used for the replace operation on top of this. Here the syntax will have the search string item as the second item in the syntax of the OREPLACE function. When the search string item is filled with NULL value then the given source string will be returned by the OREPLACE function as output. |
| Replace_string | The Replace string if the key item in the search and replace process. Here the replace string is responsible for making the replace operation to successfully happen, the replace string is the replacement item which is expected to be positioned in all the identified search string items. When the replace string is NULL then the search string will gets removed in the source string. Just as like that when the replace string is empty then also the search string will get removed from the actual string. All in cases where the replace string is not mentioned in the syntax itself then also the mentioned search string in the syntax alone will get removed from the actual string. More importantly, all the instances of the search string will get impacted during the replace process. |
Teradata OREPLACE Rules, Types, and Compliance
- The OREPLACE is compliant of ANSI SQL:2011 standard.
- The OREPLACE function supports only three key datatypes in major. They are CLOB, CHAR, and VARCHAR datatypes. When the data types other than the above types are keyed in then casting operation needs to be performed. Implicit datatype conversion is preferred mostly for this type of datatype conversion operations.
- The various types of result datatypes supported are also specific to the datatype which is been keyed in, which means if a CHAR type source string is used then the expected result type is VARCHAR. When the given string itself is of format VARCHAR then the expected output string should be of format VARCHAR too. For special cases like CLOB the expected type of results in the datatype is also expected to be of datatype CLOB format.
- There is always a restriction on the maximum size being supported on the source string value, in case the given string is greater than the expected size then an error message will be thrown by the OREPLACE function.
Examples of Teradata OREPLACE
The below section mentions various examples of OREPLACE function.
Example #1
The below given example is a string level replacement. Here the actual string to be searched, replaced, and used as a source value is directly hardcoded in the actual query itself. We can notice from the below-given query and the output snapshots that the source string ‘Hello All citizens’ is searched for the value ‘Hello’ and whenever the value ‘Hello’ is identified in the source string then those string values are replaced with the string ‘Good morning’. Hence the new string ‘Good morning citizens’ is formulated and printed onto the console.
Query:
SELECT OREPLACE ('Hello All citizens', 'Hello', 'Good mornin') as Replaced_value;Output:
Example #2
The below given example is again a string level replacement. We can notice from the below-given query and the output snapshots that the source string ‘The beautiful rings of red roses’ is searched for the value ‘red’ and whenever the value ‘blue’ is identified in the source string then those string values are replaced with the string forming the string value ‘The beautiful rings of blue roses. Here we can notice more than one instances of the string are changed.
Query:
SELECT OREPLACE ('The beautiful rings of red red roses', 'red', 'blue') as Replaced_value;Output:
Example #3
Here in this example instead of hard coding the string values an existing column from a database is selected and all values in that database column are changed. Here we are intended to change all the ‘A’ characters in the NAME column to ‘Z’.
Query:
SELECT OREPLACE (NAME, 'A', 'Z') from EDUCBA.TEST;
SELECT NAME FROM EDUCBA.TEST;Output:
Conclusion
String level functions are among the most key functionalities in the predefined functions, Teradata offers a wide variety of functions to achieve this functionality. OREPLACE is one of these functions. It has a large extent of flexibility on its use and very capable of processing even a large quantity of string and character values. These predefined function capabilities make this enterprise product to be among the superior ones in the market on its existence.
Recommended Articles
We hope that this EDUCBA information on “Teradata REPLACE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
