MySQL offers several functions for string manipulation and aggregation in queries, such as CONCAT(), CONCAT_WS(), and GROUP_CONCAT(). These tools are essential for combining text data and summarizing grouped results efficiently.

The CONCAT() Function

CONCAT() merges multiple strings into a single string. Its syntax is:

CONCAT(string1, string2, ...)

Example: In a staff table with columns given_name and family_name, create a full name:

SELECT CONCAT(given_name, ' ', family_name) AS complete_name
FROM staff;

The CONCAT_WS() Function

CONCAT_WS() joins strings with a specified separator. The syntax is:

CONCAT_WS(separator, string1, string2, ...)

Example: Combine fields from a locations table into a formatted address:

SELECT CONCAT_WS(', ', road, town, zip_code) AS address_line
FROM locations;

The GROUP_CONCAT() Function

GROUP_CONCAT() aggregates values from a group into a concatenated string. Its syntax includes optional ordering and separator:

GROUP_CONCAT(expression ORDER BY sort_expression SEPARATOR delimiter)

Example: List all staff names per department from a personnel table:

SELECT dept_id, GROUP_CONCAT(staff_name ORDER BY staff_id SEPARATOR ', ') AS team_members
FROM personnel
GROUP BY dept_id;

Practical Example: Retrieve logistics numbers for an order:

SELECT 
    o.id,
    o.order_ref AS 'Order Reference',
    GROUP_CONCAT(DISTINCT d.tracking_num ORDER BY d.tracking_num DESC SEPARATOR ', ') AS 'Tracking Numbers'
FROM 
    sales_orders o
LEFT JOIN 
    accounts a ON o.account_id = a.id
LEFT JOIN 
    employees e ON o.sales_rep_id = e.id
LEFT JOIN 
    deliveries d ON d.order_id = o.id
WHERE 
    o.order_ref = '23LGDBJ014909280017'
GROUP BY
    o.order_ref;

Common Issues and Solutions

1. Data Truncation in GROUP_CONCAT

By default, GROUP_CONCAT limits results to 1024 characters, causing truncation for large datasets.

Resolution: Check the current limit:

SHOW VARIABLES LIKE 'group_concat_max_len';

To set a higher limit globally, modify the MySQL configuration file (requires restart):

group_concat_max_len = -1  # -1 sets unlimited, or specify a length

For temporary changes with out restarting, adjust per session:

SET SESSION group_concat_max_len = -1;

2. SQL Mode Conflicts with GROUP BY

Errors like Expression #1 of SELECT list is not in GROUP BY clause occur under sql_mode=only_full_group_by.

Resolution: Globally disable this mode (requires privileges):

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

If permission is lacking, apply it session-wide:

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));