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?
Hi @Jan Loewe ,
Seems that you are using the Table Transformer macro that comes with the Table Filter, Charts & Spreadsheets for Confluence app that we develop.
The REPLACE function works only with strings, so, it is an expected behavior.
If your case looks similar to the screenshot below and you need to replace only one regex match, you may try the following workaround:
SELECT *,
REPLACE(T1.'animals', MATCH_REGEXP(T1.'animals', "\d+Cats", "g"), "Dogs") AS 'new animals'
FROM T1
To find and replace several regex matches, you'll need the REGEXP_REPLACE function or smth similar that is currently not supported by the macro - seems that it's a rare case and you are the first customer with such request.
So, I've created a new ticket for our internal roadmap and attached the link to this question - once the new function is introduced, we'll return here and notify you.
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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
There was a Search & Replace app for Confluence Server, and it was free or dirt cheap, that did Regex...
Perhaps there's a way how to get it and get it run... https://marketplace.atlassian.com/apps/1212823/search-and-replace?tab=overview&hosting=server
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jan - Welcome to the Atlassian Community!
Where are you running this?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.