Docs

Regex Replace

If you need to collectively edit values in the columns, you can use the Regex Replace transformation step. It allows you to replace a group of values that share the same pattern with a new value or just a part of the value.

Step configuration

Configuration of the Regex Replace step
Configuration of the Regex Replace step
  1. In the Data Transformation Studio, add the Regex Replace step.
  2. Indicate the columns whose values will be edited.
  3. In the Regular expression field, enter the regular expression.
  4. From the Replace dropdown list, select one of the options:
    • Matching pattern - only the part of the value which matches the regular expression is replaced.
    • Entire value in the cell - the entire content of the cell is replaced.
  5. From the with dropdown list, select one of the options:
    • Static value - the matching part or the entire value is replaced with the string you enter in the Type value field.
      If you leave the Type value field empty, the replaced part or value becomes an empty string.
    • Dynamic value - the matching part or the entire value is replaced with the result of a Jinjava insert. You can only refer to the data in the transformed file, for example, by using root['columnName']. Inserts that retrieve data from Synerise, such as profile attributes, aggregates, and expressions, aren't supported.
      For example, the {{ root["name"]|replace("Bob", "Robert") }} insert replaces all occurrences of Bob in the name column with Robert.
      For the list of inserts you can use, see Data transformation inserts.
    • No value - if you selected Matching pattern, the matching part is removed from the value. If you selected Entire value in the cell, the cell is empty.
    • UUID - the matching part or the entire value is replaced with a randomly generated UUID v4 (unique for each row).
    • Current date - the matching part or the entire value is replaced with the current date (the date and time are generated on an ongoing basis during the transformation). You can select the timezone and date format.
  6. To add more rules, click Add rule and repeat steps 2-5.
  7. To define what happens if rows contain errors, from the dropdown list next to Add rule, select one of the error handling options.
  8. Click Apply.
  9. You can check the result in the Output tab.

Selecting columns

In each rule of the step, you indicate the columns on which the operation is performed.

  1. In the header of the rule, click these and select one of the options:
    • these - the operation is performed on the columns you indicate.
    • all except these - the operation is performed on all columns except the ones you indicate.
  2. From the first dropdown list in the rule, select how you want to indicate the columns:
    • Columns - from the Select column dropdown list, select one or more columns.
    • Where name - define a condition (a dynamic condition) that the column names must meet. Select a logical operator (Ends with, Starts with, Contain, Not contain, or Regex) and enter the value. For example, if you select Starts with and enter pro, the operation is performed on the columns whose names start with pro
      Column names are case-sensitive, for example, product doesn't match ProductCode. A regular expression must match the entire column name, for example, product.* matches productName, but product doesn't. To ignore the letter case in a regular expression, add (?i) at the beginning, for example, (?i)product.*

You can't combine dynamic conditions with the all except these option.

Dynamic conditions are especially helpful when you use a JSON file as sample data. When a JSON file contains an object deeper than the root object, its items are transferred to the transformation as separate columns named {object}.{parameter}. You can't add those columns from the Select column list, but you can include them with a dynamic condition, for example, by including columns whose names contain the name of the object they were created from.

Current date option

Using the Current date option, you can enrich your data with a dynamically injected date and time which can be used, for example, to build an event salt. To define a rule with Current date:

  1. Select a timezone.
  2. Select one of the available formats:
    • ISO (for example 2022-10-05T05:55:16.786596Z)
    • Timestamp (for example 1664949342323)
    • MMM d, YYYY (for example Oct 5, 2022)
    • MMM d, YYYY, HH:mm:ss (for example Oct 5, 2022, 05:57:14)
    • YYYY-MM-dd HH:mm (for example 2022-10-05 05:57)
    • Custom - you can create your own date and time format.

Custom date format

You can use the following elements to build a custom format. The example values use October 05, 2022 as the date.

  • MMM - name of month (Oct)
  • MM - number of month (10)
  • DD - day of year (278)
  • dd - day of month (05)
  • YYYY - year (2022)
  • YY - year (22)

Handle errors

You can define what happens with rows in which the step fails (for example, as a result of Jinja rendering). From the dropdown list next to Add rule, select one of the following options:

  • Skip row if error occurred - It omits the row when an exception occurs.
  • Skip if result is null or empty string - It omits rows with empty strings and null (which may be the result of rendering Jinja).
  • Stop further transformation - The transformation stops at the row with an error. The data before the line that caused the error is transformed (and imported if the transformation is used in the Data Transformation Studio node).
  • Insert null if error occurred - Wrong values are replaced with null. If you use such a transformation for the import of profiles, the existing value of an attribute is cleared and replaced with the null value.
  • Insert empty string if error occurred - Wrong value is removed and the cell remains empty.

Example of use

You can replace the part of the domain in the email addresses of your customers, from .com to .co.uk. You can use this transformation in the further processes, for example, in importing customers to Synerise.

  1. In the Data Transformation Studio, add sample data.
  2. Click Add step and select Regex Replace.
  3. In the configuration of the step:
    1. Leave the these option at default.
    2. From the Select column dropdown list, select the column with email addresses.
    3. In the Regular expression field, enter \.com$
    4. From the Replace dropdown list, select Matching pattern.
    5. Leave Static value selected and in the Type value field, enter .co.uk
  4. Click Apply.
    Result: In the Output tab, the email addresses end with .co.uk

Canonical URL: https://hub.synerise.com/docs/automation/data-transformation-and-imports/transformations-and-data-operators/regex-replace