How to use MySQL functions in SQL macro

This article explains how to use the CONCAT, JOIN, LEFT functions of MySQL in the SQL for Confluence (Pro Edition) app.

Instructions

Follow the steps:

1. Log into the Confluence instance.

2. Go to the required confluence page and edit the same.

3. Insert a SQL macro and go to the Edit option to open the macro editor.
   
   

4. Select a Data source profile and enter the required SQL query in SQL statement. Follow this link to configure Data source profile.

   • CONCAT () - This function allows you to add two or more strings together.
     

     SELECT user_name, CONCAT (display_name,"  ", active,"  ",created_date) AS Userdetails
     FROM cwd_user;

     
     
     Click Save settings in the macro editor and Update to save the page edits.
     

   • JOIN - A JOIN clause is used to combine rows from two or more tables, based on column(s) common to both of them.
     
     In this example, a join is used on two tables cwd_user and cwd_group. The result is displayed based on the common columns in both the tables.

     SELECT user_name,last_name,group_name FROM cwd_user JOIN cwd_group on cwd_user.directory_id = cwd_group.directory_id;

     
     
       Click Save settings in the macro editor and Update to save the page edits.
     

   • LEFT () - This function is a string function that returns the left part of a string with a specified length. 
     In this example, the statement uses the LEFT function to return the 10 leftmost characters of the string group_name. The long group_name can be displayed in a short form if required.
     

     SELECT 
         group_name, LEFT(group_name, 10) summary
     FROM
         cwd_group;

     

     Click Save settings in the macro editor and Update to save the page edits.