How to use MYSQL functions in SQL query macro

This article explains how to use the functions in MySQL using the app SQL for Confluence (Pro Edition).

Instructions

Follow the steps:

Login to the Confluence instance.
Go to the required confluence page and edit the same.
Add SQL macro and go to the Edit option to open the settings for SQL settings.
Select the Data source profile and write the SQL statement as shown in the below image.

a. 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 on Save settings and Update the page.

b. JOIN - A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

In below example, we have join two tables cwd_user and cwd_group. Displayed the result based on 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 on Save settings and Update the page.

c. LEFT () - This function is a string function that returns the left part of a string with a specified length.

In below example, the following statement uses the LEFT function to return the 5 leftmost characters of the stringMySQL LEFT.
```
SELECT 
    group_name, LEFT(group_name, 5) summary
FROM
    cwd_group;
```
Click on Save settings and Update the page.