Creating a new regular expression or SQL pattern - 7.3

Talend Open Studio User Guide

Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for Data Quality
Talend Open Studio for ESB
Talend Studio
Design and Development
Last publication date
Available in...

Open Studio for Data Quality

You can create new regular expressions or SQL patterns, including those for Java to be used in column analyses.

Management processes for regular expressions and SQL patterns are the same. The procedure below with all the included screen captures reflect the steps to create a regular expression. You can follow the same steps to create an SQL pattern.


  1. In the DQ Repository tree view, expand Libraries > Patterns, and then right-click Regex.
  2. From the contextual menu, select New Regex Pattern to open the corresponding wizard.
    When you open the wizard, a help panel automatically opens with the wizard. This help panel guides you through the steps of creating new regular patterns.
  3. In the Name field, enter a name for this new regular expression.

    Do not use the following special characters in the item names: ~ ! ` # ^ * & \\ / ? : ; \ , . ( ) ¥ ' " « » < >

    These characters are all replaced with "_" in the file system and you may end up creating duplicate items.

  4. Optional: Set other metadata (Purpose, Description and Author) in the corresponding fields and click Next.
  5. In the Regular expression field, enter the definition of the regular expression to be created. The regular expression must be surrounded by single quotes.

    For the PostgreSQL database, regular expressions are not compatible among the database different versions.

    If you want to use the regular expression with PostgreSQL version 9.1 or greater, you must either:
    • in the PostgreSQL database configuration, set the standard_conforming_strings parameter to off and write double backslashes in the definition, or
    • in the Regular expression field in the wizard, use a single backslash in the expression definition.

    For further information about PostgreSQL regular expressions, select Window > Show View, expand Help and then select Bookmarks.

  6. From the Language Selection list, select the language (a specific database or Java).
  7. Click Finish to close the dialog box.
    A subfolder for this new regular expression is listed under the Regex folder in the DQ Repository tree view, and the pattern editor opens with the defined metadata and the defined regular expression.
  8. In the Pattern Definition view, click the [+] button and add as many regular expressions as necessary in the new pattern.
    You can define the regular expressions specific to any of the available databases or specific to Java.
    Note: If the regular expression is simple enough to be used in all databases, select Default from the list.
    Subfolders labeled with the specified database types or Java are listed below the name of the new pattern under the Patterns folder in the DQ Repository tree view.
  9. Save the new pattern.
    Once the pattern is created, you can drop it directly onto a database column in the open analysis editor.
  10. Optional: Click the pattern name to display its detail in the Detail View in Talend Studio.