REGEXP_REPLACE VS OTRANSLATE functions in Teradata

Like Oracle, Teradata has implemented regular expressions from Teradata 14. Since these expressions are much better than traditional functions. We can try to replace them, say OTRANSLATE can be replaced with REGEXP_REPLACE.

Below example is for your understanding.

n  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;

n  Data

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);

OTRANSLATE EXAMPLE

  SELECT

             employee_no

             ,CASE WHEN TRANSLATE_CHK(first_name USING LATIN_TO_UNICODE) <> 0 THEN 'UNK'

             WHEN OTRANSLATE( first_name,'' || OTRANSLATE( first_name,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' ,''), '')  = '' THEN 'UNK'

              WHEN OTRANSLATE( first_name,'' || OTRANSLATE( first_name,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' ,''), '') IS NOT NULL

              THEN OTRANSLATE( first_name,'' || OTRANSLATE( first_name,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' ,''), '')

               ELSE '' END AS first_name

  FROM Employee_Target ORDER BY 1;

REGEXP_REPLACE EXAMPLE

     SELECT

             employee_no

             ,CASE WHEN TRANSLATE_CHK(first_name USING LATIN_TO_UNICODE) <> 0 THEN 'UNK'

             WHEN REGEXP_REPLACE(first_name,'[^a-zA-Z0-9]+','',1,0,'i') ='' THEN 'UNK'

             WHEN REGEXP_REPLACE(first_name,'[^a-zA-Z0-9]+','',1,0,'i') IS NOT NULL

             THEN REGEXP_REPLACE(first_name,'[^a-zA-Z0-9]+','',1,0,'i')

              ELSE '' END AS first_name

   FROM Employee_Target ORDER BY 1;

Note:

I have added TRANSLATE_CHK function to avoid “The string contains an untranslatable character” error.

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.