Concatenating Multiple Columns in MySQL: A Beginner’s Guide 🔗

Learn how to concatenate multiple columns in MySQL using the CONCAT() function. This tutorial provides examples, exercises, and solutions to help you master this essential MySQL skill.

Syntax 🔗

In MySQL, we can use the CONCAT() function to combine the values of two or more columns into a single string column.

The basic syntax for concatenating multiple columns is:

SELECT CONCAT(column1, column2, ..., columnN) FROM table_name;

Where:

• column1, column2, …, columnN are the columns you want to concatenate.
• table_name is the name of the table that contains the columns.

Examples of Concatenating Multiple Columns 🔗

Let’s consider a simple example. Suppose we have a table employees with columns first_name and last_name, and we want to concatenate these two columns to create a full name.

employees table structure:

The employees table has three columns:

• id: an integer column representing the employee ID
• first_name: a string column representing the employee’s first name
• last_name: a string column representing the employee’s last name

Sample data:

The employees table contains the following data:

+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 1  | John       | Smith     |
| 2  | Jane       | Doe       |
| 3  | Bob        | Johnson   |
+----+------------+-----------+

To concatenate the first_name and last_name columns, we can use the following query:

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

The result would be:

+------------+
| full_name  |
+------------+
| John Smith |
| Jane Doe   |
| Bob Johnson |
+------------+

Note the space character (' ') between the first_name and last_name columns. This is used to separate the two values in the resulting string.

Concatenating Multiple Columns with Separator 🔗

In the previous example, we used a space character (' ') as a separator between the columns. You can use any character or string as a separator, not just a space or comma.

For example, let’s say we want to concatenate the first_name, middle_name, and last_name columns with a comma (,) as a separator:

SELECT CONCAT(first_name, ', ', middle_name, ', ', last_name) AS full_name FROM employees;

This would result in:

+----------------------+
| full_name           |
+----------------------+
| John, M, Smith       |
| Jane, A, Doe        |
| Bob, R, Johnson     |
+----------------------+

Concatenating with Null values 🔗

What if one of the columns contains null values? When concatenating columns with null values, the resulting string will be null if any of the columns contain null values. This is because the CONCAT() function returns null if any of its arguments are null. Let’s see what happens:

CREATE TABLE employees (
  id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  middle_name VARCHAR(50)
);

INSERT INTO employees (id, first_name, last_name, middle_name) VALUES
  (1, 'John', 'Smith', NULL),
  (2, 'Jane', 'Doe', 'A'),
  (3, 'Bob', 'Johnson', 'R');

SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name) AS full_name FROM employees;

The result would be:

+------------+
| full_name  |
+------------+
| NULL       |
| Jane A Doe   |
| Bob R Johnson |
+------------+

As you can see, the CONCAT() function returns null for the first row because the middle_name column is null.

Using concatenation with other string functions 🔗

You can combine the CONCAT() function with other string functions to achieve more complex string manipulations. For example:

SELECT CONCAT(UPPER(first_name), ' ', LOWER(last_name)) AS full_name FROM employees;

This would result in:

+------------+
| full_name  |
+------------+
| JOHN smith |
| JANE doe   |
| BOB johnson |
+------------+

In this example, we used the UPPER() function to capitalize the first name and the LOWER() function to lowercase the last name.

Common Use Cases of Concatenating Multiple Columns 🔗

Concatenating multiple columns is a common operation in various real-world scenarios, such as:

• Creating a full name from separate first and last name columns
• Generating a unique identifier by combining multiple columns
• Creating a string representation of a complex data structure
• Generating a report or summary from multiple columns

For example, in a customer database, you might want to concatenate the first_name, middle_name, and last_name columns to create a full name. In an e-commerce platform, you might want to concatenate the order_id, customer_name, and order_date columns to generate a unique order identifier.

Exercises and Solutions 🔗

1. Simple Concatenation: Create a table students with columns first_name and last_name. Concatenate these two columns to create a full name.
2. Concatenation with Separator: Modify the previous exercise to use a hyphen (-) as a separator between the first_name and last_name columns.
3. Multiple Column Concatenation: Create a table orders with columns order_id, customer_name, and order_date. Concatenate these three columns to create a string in the format order_id - customer_name - order_date.

Solutions

1. Simple Concatenation

CREATE TABLE students (
  id INT,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

INSERT INTO students (id, first_name, last_name) VALUES
  (1, 'John', 'Smith'),
  (2, 'Jane', 'Doe'),
  (3, 'Bob', 'Johnson');

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM students;

2. Concatenation with Separator

SELECT CONCAT(first_name, '-', last_name) AS full_name FROM students;

3. Multiple Column Concatenation

CREATE TABLE orders (
  order_id INT,
  customer_name VARCHAR(100),
  order_date DATE
);

INSERT INTO orders (order_id, customer_name, order_date) VALUES
  (1, 'John Smith', '2022-01-01'),
  (2, 'Jane Doe', '2022-01-15'),
  (3, 'Bob Johnson', '2022-02-01');

SELECT CONCAT(order_id, ' - ', customer_name, ' - ', order_date) AS order_string FROM orders;

Conclusion 🔗

In this tutorial, you learned how to use the MySQL CONCAT() function to concatenate multiple string columns together. You went through the basic syntax, demonstrated how to include separators, and handled cases with null values. Additionally, you combined CONCAT() with other string functions for more complex manipulations and learned about common real-world scenarios where concatenating columns is useful.