Editing the pattern indicator and using it in a column analysis - 7.1

Talend Data Management Platform Studio User Guide

Version
7.1
Language
English (United States)
Product
Talend Data Management Platform
Module
Talend Studio
Content
Design and Development
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

  1. In the Profiling perspective, expand Libraries > Indicators > System Indicators.
  2. In the Pattern Matching folder, double-click Regular Expression Matching.
  3. In the Indicator Definition view, click [+].
  4. In the new indicator line, select Teradata from the Database list.
  5. Click Edit... next to the new field to display the Edit expression dialog box.
  6. 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__%>
  7. Add the modified Pattern indicator into a column analysis.
  8. Run the analysis and switch to the Analysis Results page.
  9. 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 )