Table Transformer: Regex replace

Question

I would like to perform a replace in a result using a regular expression. Unfortunately something like

SELECT T1.'animals'->replace('/Cats/g',"Dogs") FROM T1

does not seem to work, because it seems that I cannot convince JavaScript to treat the search string as a regular expression.

Any ideas how to solve that?

Stiltsoft support · Answer

Hi @Jan Loewe ,

We are here with the updates: today we've released a new version of the app (for Data Center it's 12.1.2) with the supported REGEXP_REPLACE function inside the Table Transformer macro. Hope it comes in handy for your further cases.

SELECT *,
REGEXP_REPLACE(T1.'animals', "\d+Cats", "Dogs", "g") AS 'new animals'
FROM T1

Stiltsoft support · Answer

As I see, we do have a simplified example with the SEARCH function in our documentation https://docs.stiltsoft.com/tfac/dc-server/custom-transformation-use-cases-with-advanced-sql-queries-42241587.html#CustomTransformationusecaseswithadvancedSQLqueries-Splittingcellvaluesinacolumntodifferentrows

and you've amplified it with REPLACE and MATCH_REGEXP that are currently supported.

Thank you for the thorough description! It may definitely help other users with similar use cases.

Jan Loewe · Answer

Thanks for the reply, I'll be looking forward to the new functionality.

For the records, in the meantime, I found some kind of workaround using SEARCH:

I go from:

To:

Using:

SEARCH / AS @a EX(IFNULL('log'->'split'(";"), " "->'split'("."))) /
RETURN(@a->'id' AS id, REPLACE(_, MATCH_REGEXP(_, "$\d{2}:\d{2}$"), "") AS 'log')
FROM T1

This is btw. my use-case: I have to strip timestamps from multiple entries which are unfortunately in a single field.

Finally, I join the results again using a pivot table:

Which finally yields the desired result: