Before being able to use regular expressions on analyzed columns in a database, you
must first declare the created regular expression function by adding the SQL instruction
and the pattern.
Procedure
-
In the Profiling perspective, expand .
-
In the Pattern Matching folder, double-click
Regular Expression Matching.
-
In the Indicator Definition view, click
[+].
-
In the new indicator line, select Teradata from the
Database list.
-
Click Edit... next to the new field to display the
Edit expression dialog box.
-
In the Expression box, add the following SQL
expression:
SELECT COUNT(CASE WHEN Regex_INSTR(<%=__COLUMN_NAMES__%>,<%=__PATTERN_EXPR__%>) =1 THEN 1 END), COUNT(*)FROM <%=__TABLE_NAME__%> <%=__WHERE_CLAUSE__%>
-
Add the modified Pattern indicator into a column analysis.
-
Run the analysis and switch to the Analysis Results
page.
-
In the Pattern Matching table, right-click the pattern
results and select View invalid rows, for example. The
SQL editor opens listing invalid data and the SQL
expression looks like the following:
SELECT * FROM "test"."Employee" WHERE ( not REGEX_INSTR("FULLNAME" , '^[A-Z]+' ) =1 OR "FULLNAME" IS NULL )