OREPLACE VS OTRANSLATE function in Teradata

OTRANSLATE

·         Returns source_string with every occurrence of each character in from_string replaced with the corresponding character in to_string.

·         OTRANSLATE is an embedded services system function. It can be activated by invoking embedded services functions

·         Expressions passed to this function must have one of the following data types: CHAR or VARCHAR

SELECT OTRANSLATE(' Current Year 2013 ',  '3', '4');

The occurrence in source_string of the character in from_string ('3') is replaced by the character in to_string ('4').

Otranslate also answers below queries:

ü  How to strip Special Characters in Teradata

ü  How strip only characters data from VARCHAR field in Teradata

ü  How to extract numbers from strings in Teradata

Let me walkthrough with some examples by having some data in volatile table

CREATE           VOLATILE  TABLE Employee_Target, FALLBACK,

NO BEFORE JOURNAL,

NO AFTER JOURNAL

(

employee_no INTEGER,

dept_no NUMBER,

first_name VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

last_name CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

salary DECIMAL(10,2))

UNIQUE PRIMARY INDEX(employee_no)

            ON       COMMIT PRESERVE ROWS;

INSERT INTO Employee_Target

VALUES           (1, 999, 'S22pot  ', 'Paul', NULL);

INSERT INTO Employee_Target

VALUES           (2, 999 ,'Arfy123' ,'Paul', NULL);

INSERT INTO Employee_Target

VALUES           (3, 50, 'M!ke', 'Larkins', 245098.00);

INSERT INTO Employee_Target

VALUES           (4, '40', 'S@ra','Wilson', 97450.75);

INSERT INTO Employee_Target

VALUES           (5, '40', 'Marsha', 'Lewis', 98453.88);

Example 1:

For stripping all special characters from the column first_name

SEL

employee_no

,first_name

 ,OTRANSLATE(first_name,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' ,'') AS O_first_name

 ,OTRANSLATE(first_name,'' || OTRANSLATE(first_name,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' ,''), '') AS OO_first_name

 FROM    Employee_Target;                         

Example 2:

For stripping only characters by removing special characters and numeric values in first_name

SEL

employee_no

,first_name

 ,OTRANSLATE(first_name,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' ,'') AS O_first_name

 ,OTRANSLATE(first_name,'' || OTRANSLATE(first_name,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' ,''), '') AS OO_first_name

 FROM    Employee_Target;                             

Example 3:

For stripping only numeric by removing special characters and characters values in first_name

SEL

employee_no

,first_name

,OTRANSLATE(first_name,'0123456789' ,'') AS O_first_name

,OTRANSLATE(first_name,'' || OTRANSLATE(first_name,'0123456789' ,''), '') AS OO_first_name

 FROM    Employee_Target; 

OREPLACE

·         Replaces every occurrence of search_string in the source_string with the replace_string. You use this function either to replace or remove portions of a string.

·         OTRANSLATE is an embedded services system function. It can be activated by invoking embedded services functions

·         Expressions passed to this function must have one of the following data types: CHAR, VARCHAR, or CLOB

·         OREPLACE provides a superset of the functionality provided by the TRANSLATE function. TRANSLATE provides single character, 1-to-1 substitution while OREPLACE allows you to substitute 1 string for another, as well as to remove character strings.

SELECT OREPLACE('Current Year 2013 ', '2013', '2014');

The string '2013' in the source string was replaced by the string '2014'.

Some more examples

SELECT OREPLACE('This water is a pure water', 'water', 'juice');

Output is This juice is a pure juice, which means multiple words can also be replaced

Now let us see how Null values can be handled via OREPLACE

SELECT OREPLACE('Can I replace this word pass', 'pass', NULL);

SELECT OREPLACE('Can I replace this word pass', 'pass', '');

Above two queries would return as Can I replace this word, Which means you cannot substitute a NULL

Can we Replace Null like a Coalesce Function?

SELECT OREPLACE(NULL, NULL, 'UNKNOWN');

The Answer would be No, the result of above query would be Null only.