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 Libraries > Indicators > System Indicators.
- 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 )