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. Save the macro and the page.