Using basic regular expressions

Use case:

You need to find the first four values in the range from 0 to 8 in an 'ID' column.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table within the macro body.
  3. Select the macro and click Edit.
  4. Switch to the SQL query tab.

  5. Enter the following SQL query:

    SELECT
'ID'->match("^[0-8]{4}") AS 'Year', 'Brand'
FROM T1
    SQL
  6. Click Next.
  7. Save the macro and the page.

The [0-9] expression is used to find any character between the brackets.
Use the [^0-9] expression to find any character that is NOT a digit.

[aeiou] matches any one of the charachers within the the square bracket.
[a-zA-Z]  matches any uppercase or lowercase letters.
^ matches the beginning of the input string,
{4} means the number of characters to display

Here you can find more info about JavaScript regular expressions.

You can use FORMATWIKI function for the purposes of cell formatting.

Searching in the string with a regular expression

Use case:

You need to search for a specific pattern in the string and display the matching parts in a new column.

For example, among the "LettersNumbers" (A10, B12, UT7) components you need to find all the "Numbers/Numbers" (08/15, 10/11) components and show them in a separate column.

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 *,
MATCH_REGEXP(T1.'Component', "\d+/\d+", "g")
AS 'Component with /'
FROM T*
    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.

Sorting by complex version formats

Use case:

You need to order the table data by the column containing complex version formats ( i.e. v1.6.151, etc).

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table within the macro body.
  3. Select the macro and click Edit.
  4. Switch to the SQL query tab.

  5. Enter the following SQL query:

    SELECT * FROM T1 
ORDER BY 
(TEXT(IFNULL(T1.'versions', 0))->match("([0-9]+)")->1::number +
IFNULL(TEXT(IFNULL(T1.'versions', 0))->match("[0-9]+.([0-9]+)")->1, 0) / 1000 +
IFNULL(TEXT(IFNULL(T1.'versions', 0))->match("[0-9]+.[0-9]+.([0-9]*)")->1, 0)/1000000) ASC
    SQL
  6. Click Next.
  7. Save the macro and the page.