Confluence table transformer - Access href and text properties of link to rename hyperlink

[Jannis Louw]

I'm using Confluence table transformer. I would like to rename an existing hyperlink as part of a query. Is there a way to access href and text properties of an existing link to rename the hyperlink? I need this in order to build a new FormatWiki based hyperlink with the updated text title.

[Katerina Kovriga _Stiltsoft_ · Accepted solution]

Hi @Jannis Louw ,

I'm not sure if I've gotten your case right but here is the option that I can suggest (if it doesn't suit you please clarify the question and attach some screenshots).

So here is your page in the edit mode:

The first column contains raw links that you manually paste for your wiki page. The second column provides description for your links.

The links in the first column can be often changed and it's not convenient to rename them manually. And, of course, raw links look not so appealing.

If this is the case, you may try the following SQL query:

SELECT
FORMATWIKI("[Click here|" + T1.'Regular link' + "]")
AS 'Check the required info',
T1.'Description'
FROM T1

Here we renamed all the raw links from the first column and hid the original raw view.

And if you need to construct (build) links from multiple text pieces put together (where the one changing piece comes from a table column) please follow this example.

[Jannis Louw]

This will get you the text and link properties of a hyperlink where header is Link:

SELECT *,
FORMATWIKI( "[" + T1.'Link'->tfView->match("href=([^ >]*)")->1->slice(1, -1) + "|" +
T1.'Link'->tfView->match("href=([^ >]*)")->1->slice(1, -1) + "]" ) AS 'New Link',
T1.'Link'->tfView->match("[^>]+>([^<]+)")->1 AS 'Text'
FROM T*

[Jannis Louw]

Hi @Katerina Kovriga _Stiltsoft_ 

This is very useful - thanks. My problem is however slightly different in that my table has links similar to the "Click here" link in your last screenshot - the link text is "Click Here" and does not contain the full address although clicking on the URL will take me to the desired location (as the address is set). I want to rename the "Click here" link text to something else without losing the hyperlink (link address). 

In order to do so using FormatWiki I need a way to access the Address property of "Click Here". 

Is there any way to do that? 

Thanks, Jannis

[Katerina Kovriga _Stiltsoft_]

Hi @Jannis Louw ,

I've consulted with our developers and got a more complex SQL query that may suit your case:

SELECT *,
FORMATWIKI( "[Bla-bla link|" +
T1.'Link'->tfView->match("href=([^ >]*)")->1->slice(1, -1) + "]" ) AS 'New Link'
FROM T*

Now the Link and the New Link columns mirror each other and contain the same corresponding links (and you may hide the Link column, of course).

[Jannis Louw]

This works perfectly, thank you so much!