A database table may contain any data according to the data types specified for the columns. The same can be retrieved through SQL statements and can be used in Confluence pages by using the SQL for Confluence app. When the table data is having link(s), the query results display the data as a plain text but not as clickable links. This article explains how to make the links in a database table clickable to be able to navigate to the respective address upon clicking them using SQL for Confluence.
- Add a SQL query macro in the intended Confluence page.
Add SELECT statement as given below in the macro:
select ID, CONCAT('[',Link,']') as url from pagelinks;
In this sample query, pagelinks is the table name, Link is the column that has links to other confluence pages in the instance.
- Set the output format of the SQL macro to Wiki as shown in the screenshot below:
- The output is shown as mentioned in:
If a table has empty or blank strings, the cell has the text "[ ]". To display null value when the column is empty, modify the SQL query as given below
select CASE WHEN "url" = '' THEN NULL ELSE CONCAT ('[',url,']') END as Link, id from pagelinks;
The mentioned SQL query is tested successfully on Mysql and Postgres databases.
It is recommended to try this function in a test environment before deploying it in production.