Table Transformer: Regex replace

Jan Loewe
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
September 13, 2024

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?

3 answers

3 votes
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 13, 2024

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

Fri 4-1.png

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.

Jan Loewe
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
September 17, 2024

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:

1.png

To: 

2.PNG

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:

 4.PNG5.PNG

Which finally yields the desired result:

6.PNG

Like Stiltsoft support likes this
Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 17, 2024

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.

Stiltsoft support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 31, 2024

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

Thu 4-1.png

0 votes
Kristian Klima
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
September 13, 2024

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

0 votes
John Funk
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
September 13, 2024

Hi Jan - Welcome to the Atlassian Community!

Where are you running this? 

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events