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.
