MySQL CONCAT And GROUP_CONCAT Functions With Examples

This tutorial explains how to use MySQL CONCAT with Select and GROUP_CONCAT functions with syntax and practical examples:

CONCAT is a String function supported by MySQL to combine or join two or more Strings together and return as a single value. The name CONCAT comes from the verb concatenation, which means joining 2 or more entities together.

In this tutorial, we will learn the usage of CONCAT with query examples and other variations of the CONCAT function provided by MySQL.

=> Click here for the complete MySQL tutorial series

MySQL CONCAT Function

Syntax:

The syntax of the CONCAT function is straightforward. It just contains a comma-separated list of the Strings that needs to be joined.

CONCAT(string1, string2, ------ stringN)

Both the input and output types expected by the CONCAT function are Strings. Even if it is supplied with numbers, the final output will be String.

For example:

#1) With input types as Strings.

SELECT CONCAT("string1", "string2");

//Output
string1string2

#2) With input as numbers/floating-point numbers.

SELECT CONCAT(1,2);

//Output
12

SELECT CONCAT(1.1234,2);

//Output
1.12342

Using CONCAT With SELECT Statements

CONCAT is most widely used alongside the SELECT queries, where it can combine data from 2 or more columns into a single column.

A classic example can be, suppose we have a table that has separate columns for firstName and lastName fields. So while displaying data, suppose it’s the desire to show the FullName instead of firstName and lastName. We can make use of CONCAT and display the selected data accordingly.

Let’s see this in action.

First, create a Student table with fields – id, first name, last name, age, date of birth & department.

CREATE TABLE student (id INT PRIMARY KEY, fname VARCHAR(100), lname
 VARCHAR(100), age INT, dob DATE, department VARCHAR(100));

Insert some dummy data into the table.

INSERT INTO student values 
(1,'Darren', 'Still', 32, '1988-05-20', 'ENGINEERING'),
(2,'Abhishek', 'Kumar', 28, '1992-05-20', 'ACCOUNTING'),
(3,'Amit', 'Singh', 30, '1990-09-20', 'ENGINEERING'),
(4,'Steven', 'Johnson', 40, '1980-05-21', 'HUMAN RESOURCES'),
(5,'Kartik', 'Shamungam', 20, '2000-05-12', 'TRAINEE');

Now, write a SELECT query to get a full name as a concatenated string combining first name and last name.

SELECT CONCAT(fname,lname) as fullName from student

//Output

As you can see in the above output, there’s no spacing between first name and last name, which is making it unreadable. We can add spacing by updating the CONCAT function to have an extra space character as a string to be concatenated.

SELECT CONCAT(fname, ' ', lname) as fullName from student

This would ensure that with each entry, you would have extra spacing in between.

Using CONCAT With GROUP

MySQL provides another function called GROUP_CONCAT.

It’s similar to CONCAT, but it differs in the way that CONCAT is used to combine values across columns, while the GROUP_CONCAT function is mostly used for concatenating values across rows.

MySQL GROUP_CONCAT Syntax

SELECT col1, col2, ..., colN
GROUP_CONCAT ( [DISTINCT] col_name1 
[ORDER BY clause] [SEPARATOR str_val] ) 
FROM table_name GROUP BY col_name2;

So, in the GROUP_CONCAT function, you can see:

• col_name: This is the column with which you want to concatenate. There’s an optional DISTINCT clause to avoid repeating values.
• ORDER BY: The ORDER BY clause is used to specify the order within the concatenated list and is optional.
• SEPARATOR: This is again an optional clause that can be used to define a custom separator between the concatenated values. By default, comma(,) is the separator.

MySQL GROUP_CONCAT Examples

In the Student table example above, suppose we want to find out a list of concatenated departments.

SELECT GROUP_CONCAT(department) as departments FROM student

//Output
ENGINEERING,ACCOUNTING,ENGINEERING,HUMAN RESOURCES,TRAINEE

In the above query, 

• The output contains a comma-separated list of all the departments that are available in the department column.
• Also, there are repeating values (For example, ENGINEERING) as we have not specified a DISTINCT clause.

Let’s try the same example with the DISTINCT clause:

SELECT GROUP_CONCAT(DISTINCT department) as departments FROM student

//Output
ACCOUNTING,ENGINEERING,HUMAN RESOURCES,TRAINEE

This would just return the distinct values of the department column.

Now add a custom separator as ‘|’ and ORDER BY clause to have department names sorted in ascending order

SELECT GROUP_CONCAT(DISTINCT department separator ' | ') as departments FROM student

//Output
ACCOUNTING | ENGINEERING | HUMAN RESOURCES | TRAINEE

So, in the above query,

• Department names are sorted in ascending order.
• No repeating values are returned.
• The separator is changed from ‘,’ to ‘ | ‘.

Let’s see another example to list down the concatenated values of students in each department.

Here we would not want as DISTINCT as 2 students in a department might have the same name.

SELECT department, GROUP_CONCAT(fname ORDER BY fname ASC SEPARATOR ' | 
') AS students FROM student GROUP BY department

// Output

Combining CONCAT And GROUP_CONCAT

In the above example, suppose we want to show the CONCATENATED values of name and last name along with each department.

To achieve this, we can use CONCAT within the GROUP_CONCAT command.

Let’s see this in action below :

SELECT department, GROUP_CONCAT(CONCAT(fname, ' ', lname) order by fname
 asc SEPARATOR ' | ') as students from student group by department

//Output

Handling NULL Values With CONCAT

CONCAT expects String arguments and returns the output as String.

If any input to the CONCAT function is NULL, then the output would be NULL as well.

SELECT CONCAT("string1","string2", NULL);

//Output
NULL

In order to handle NULL values, you can make use of an ifNull statement within the CONCAT function which would ensure a default or blank value in case of the NULL value from the column.

Suppose we have 2 Strings and 1 NULL value to CONCAT, like the above example.

SELECT CONCAT("string1","string2", ifNull(NULL,'hello'));

//Output
string1string2hello

In the above query, we have added a ifNull function surrounding the NULL value (or a column which could be NULL) – if the NULL match is successful, then it would return ‘hello’ else the actual column value.

So in the output, you can see for the NULL string, ‘hello’ is printed.

Frequently Asked Questions

Q #1) How do I merge columns in MySQL?

Answer: MySQL provides a CONCAT function to combine or merge 2 or more columns from one or more tables and return the merged values in the output.

Q #2) Where and when I have to use the concat method in MySQL?

Answer: CONCAT method is generally used against the display of query results where you would want to join 2 or more columns and represent them as a single column.

For example, suppose you have a table which contains the first name and last name as separate columns and you want to display them as a single entity called fullName – then you can use CONCAT function to merge the values of first name and last name column and display them together as a single column.

Q #3) What is MySQL GROUP_CONCAT?

Answer: Similar to CONCAT, MySQL GROUP_CONCAT is also used to concatenate values across a table. The difference here is while CONCAT is used to combine values across columns, GROUP_CONCAT gives you the capability to combine values across rows.

It’s also important to note that both GROUP_CONCAT and CONCAT can be combined to return desired results.

GROUP_CONCAT is used generally in scenarios where you would want to GROUP or combine values across rows. For example – you have a product table with product name and category and you want to list down all the products against a given category as comma-separated-values -then you can use GROUP_CONCAT.

Query:

SELECT categoryName, GROUP_CONCAT(itemName) AS itemList FROM products 
GROUP BY categoryName

Q #4) How can I specify a separator using the CONCAT command?

Answer: With CONCAT, you can specify separator as a separate string to be concatenated.

For example: Suppose you want to use ‘|’ as a separator then in the CONCAT command you can specify this string between the column names you are concatenating.

SELECT CONCAT(fname, '| ', lname) as fullName from student

Q #5) What is the difference between CONCAT and CONCAT_WS commands?

Answer: CONCAT_WS is another variation of CONCAT provided by MySQL, which enables a user to specify a separator for the columns that are being concatenated.

This is preferred over CONCAT in situations where you want to merge more number of columns and use the same separator for all the columns that are concatenated.

Example: Suppose there’s a table student with fields – fname, lname, and address.

Now we want to concatenate all these three fields and separate by ‘|’.

Using CONCAT, we need to specify the separator as a separate string to be concatenated.

SELECT CONCAT(fname, '|', lname, '|', address) as mergedColumn from student

While with CONCAT_WS you would just need to specify the separator once.

SELECT CONCAT_WS('|', fname, lname, address) as mergedColumn from student

Conclusion

In this tutorial, we learned about the MySQL CONCAT function and its usage. This function is generally very helpful when displaying query results for merging values against different columns.

We also learned about 2 different variations of the CONCAT function – one is concatenating with a separator using CONCAT_WS and another is concatenating values of rows using the MySQL GROUP_CONCAT function.