Searching and replacing with a regular expression

Use case

You need to search for a specific pattern in the column and replace the found parts with another string.

For example, in the 'Description' column you need to find all the "SOCsome_numbers" (SOC2, SOC5) parts and replace them with the "PIR01-new" strings.

Solution

1. Switch the page to the edit mode.

2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.

3. Select the macro and click Edit.

4. In the Presets tab select Custom transformation and click Next.

5. Enter the following SQL query:

   SELECT *,
   REGEXP_REPLACE(T1.'Description', "SOC\d+", "PIR01-new", "g") AS 'Description'
   FROM T1

   SQL

   Here the "\d+" regular expression stands for any number of digits. To learn more about regular expressions, you may check this guide.

6. Save the macro and the page.