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.
Before you begin
- You have created a regular expression on Teradata.
- You have selected the
Profiling
perspective.
Procedure
-
In the DQ Repository tree view, expand .
-
Double-click Regular Expression Matching, or right-click it
and select Open from the contextual menu.
The corresponding view displays the indicator metadata and its
definition.
-
Scroll down to the Teradata line.
-
Click Edit....
The Edit expression dialog box opens.
-
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 a pattern indicator into a column analysis:
-
In the Analysis Setting page, expand the
Data preview part.
-
Click Select indicators.
-
Expand Patterns and select the patterns you
need.
The new expression will be used no matter what patterns you are
selecting.
-
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 )
