SQL SEPARATOR

What is the SQL SEPARATOR keyword and how do I use it?

In MySQL and MariaDB, SEPARATOR is an option inside GROUP_CONCAT that sets the string placed between concatenated values.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SEPARATOR: Supported: MySQL, MariaDB (GROUP_CONCAT with SEPARATOR). Not a keyword: PostgreSQL, SQL Server, Oracle - use STRING_AGG or LISTAGG with a delimiter argument.

SQL SEPARATOR Full Explanation

SEPARATOR is an optional clause of the GROUP_CONCAT aggregate function in MySQL and MariaDB. By default, GROUP_CONCAT inserts a comma between values. Adding SEPARATOR 'str' changes that delimiter, and SEPARATOR '' removes it entirely. The output length is limited by the group_concat_max_len system variable, which defaults to 1024 bytes. Increase it at the session or global level when aggregating many or long values.

SEPARATOR is a reserved keyword in MySQL. It is not a standalone function or statement. Other databases support delimiter-based aggregation but do not use the SEPARATOR keyword. PostgreSQL and SQL Server pass a delimiter argument to STRING_AGG, and Oracle passes a delimiter to LISTAGG. For row-wise concatenation with a fixed separator, use CONCAT_WS, which takes the separator as its first argument and does not aggregate multiple rows.

SQL SEPARATOR Syntax

GROUP_CONCAT([DISTINCT] expr [ORDER BY sort_expr [ASC|DESC] ...] [SEPARATOR 'str']); -- Default comma
SELECT GROUP_CONCAT(col) FROM t; -- Custom delimiter
SELECT GROUP_CONCAT(col SEPARATOR ' | ') FROM t; -- No delimiter
SELECT GROUP_CONCAT(col SEPARATOR '') FROM t;

SQL SEPARATOR Parameters

expr (expression) - Value to concatenate from rows in the group
DISTINCT (modifier) - Removes duplicate values before concatenation
ORDER BY (clause) - Sort order of values prior to concatenation
SEPARATOR (string literal) - String inserted between values - default is comma - use '' to remove
group_concat_max_len (system variable) - Maximum byte length of the result - default 1024 - increase per session if needed

Example Queries Using SQL SEPARATOR

/* Example 1 - join item names per order with a custom separator */
SELECT o.id, GROUP_CONCAT(oi.item_name ORDER BY oi.position SEPARATOR ' - ') AS item_names
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id; /* Example 2 - newline-separated list of tags */
SELECT GROUP_CONCAT(tag SEPARATOR '\n') AS tags_block
FROM post_tags
WHERE post_id = 42; /* Example 3 - remove the delimiter completely */
SELECT GROUP_CONCAT(code SEPARATOR '') AS glued
FROM parts
WHERE batch_id = 9; /* Example 4 - large outputs may require raising the limit */
SET SESSION group_concat_max_len = 1000000;
SELECT GROUP_CONCAT(stmt SEPARATOR ' ') AS ddl_script
FROM sys_generated_ddls
ORDER BY id;

Expected Output Using SQL SEPARATOR

One string per group with values joined by the chosen delimiter.
No trailing delimiter at the end of the string.
NULL inputs are skipped and do not produce extra delimiters.
Result may be truncated if it exceeds group_concat_max_len.

Use Cases with SQL SEPARATOR

Display a user's roles or tags as a single field.
Create readable lists in reports or CSV exports.
Assemble DDL or SQL snippets into a copyable script.
Generate newline or JSON-like blocks for downstream tools.

Common Mistakes with SQL SEPARATOR

Assuming SEPARATOR exists in PostgreSQL, SQL Server, or Oracle - those engines use delimiter arguments in STRING_AGG or LISTAGG instead.
Forgetting to raise group_concat_max_len for large results, which silently truncates output.
Passing non-literal expressions where a literal separator is expected.
Confusing GROUP_CONCAT with CONCAT_WS - CONCAT_WS is row-wise and does not aggregate multiple rows.

First Introduced In

MySQL 4.1 - GROUP_CONCAT added with SEPARATOR clause

Frequently Asked Questions

How do I change the delimiter used by GROUP_CONCAT?

Use the SEPARATOR clause, for example: GROUP_CONCAT(col SEPARATOR ' | '). The default is a comma, and SEPARATOR '' removes the delimiter.

Why do I get truncated GROUP_CONCAT output?

The result is limited by group_concat_max_len, which defaults to 1024 bytes. Increase it for long strings with SET SESSION group_concat_max_len = 1000000.

Do other databases support SEPARATOR?

PostgreSQL and SQL Server do not use the SEPARATOR keyword. They accept a delimiter argument in STRING_AGG, and Oracle uses LISTAGG with a delimiter argument.

When should I use CONCAT_WS instead of GROUP_CONCAT?

Use CONCAT_WS for row-wise concatenation where the separator is fixed and you are not aggregating multiple rows. Use GROUP_CONCAT to aggregate many rows into one string.