MySQL CONCAT to Concatenate Strings

This tutorial explains MySQL CONCAT() which is a built-in String function. It takes variable no. of strings as input and concatenates them together. We’ll describe the usages of this method with the help of simple examples.

MySQL string concatenation is more user-friendly than other databases such as PostgreSQL or Oracle. They provide a string concatenation operator “||” instead of a proper function. However, MS SQL server does the same job using the addition arithmetic operator (+).

1. CONCAT() Syntax
2. CONCAT() Simple Examples
4. CONCAT() with Tables

Let’s now go through each of the section one by one.

MySQL CONCAT() Function

As stated initially, CONCAT() is a built-in MySQL function which accepts one more quoted strings as input and joins them together. It combines them one by one in the given order.

So, let’s now see the details of MySQL CONCAT and check out how can we use it.

Syntax

Below is the signature of this method:

CONCAT('string1', 'string2', ... );

Below are the descriptions of the parameters in the above function.

+----------+------------------------------+
| # Param  | # Description                |
+----------+------------------------------+
| string   |  A string for concatenation. |
+----------+------------------------------+

The CONCAT function first converts every argument to the string type before it does the concatenation. If any parameter is NULL, then it returns a NULL value.

Let’s now understand more about it with examples.

MySQL CONCAT() Examples

The following command concatenates two quoted words: Python and MySQL. However, we also added a space to separate two string.

SELECT CONCAT('Python', ' ', 'MySQL');

After execution, it gives the following result:

Python MySQL

If we supply a NULL value, then the CONCAT() function would provide us with a NULL in return. See below example:

SELECT CONCAT('Python', NULL, 'MySQL');

The outcome:

NULL

MySQL CONCAT() with Tables

Let’s now create a table, populate some data, and see how does MySQL concatenates strings.

CREATE TABLE EMPL
    (
        empl_id int NOT NULL,
        empl_first_name VARCHAR(20),
        empl_middle_name VARCHAR(20),
        empl_last_name VARCHAR(20),
        empl_address VARCHAR(40),
        empl_pin VARCHAR(10)
    );

INSERT INTO EMPL
    (
        empl_id, empl_first_name, empl_middle_name, empl_last_name, empl_address, empl_pin
    )
VALUES
    (1, 'Amit', 'Kumar', "Singh", "Sec-62, Noida", "201301"),
    (2, 'Jyoti', 'Rani', "Saini", "Sec-21, Noida", "201301"),
    (3, 'Vimal', 'Kumar', "Sood", "Sec-11, Noida", "201301"),
    (4, 'Neelam', 'Jai', "Singh", "Sec-23, Noida", "201301");

SELECT * FROM EMPL;

When you would run the given SQL commands, it gets you the following output:

1	Amit	Kumar	Singh	Sec-62, Noida	201301
2	Jyoti	Rani	Saini	Sec-21, Noida	201301
3	Vimal	Kumar	Sood	Sec-11, Noida	201301
4	Neelam	Jai	Singh	Sec-23, Noida	201301

You can observe that to see the full name of employees, you have to use the CONCAT function to combine first, middle, and last names separated with spaces.

Here’s the MySQL query to print the full name of all employees:

SELECT 
    CONCAT(empl_first_name, ' ', empl_middle_name, ' ', empl_last_name) Fullname
FROM
    EMPL;

After running it, you can list down all names in full. Check below output.

Amit Kumar Singh
Jyoti Rani Saini
Vimal Kumar Sood
Neelam Jai Singh

Similarly, if you wish to display the complete address, then also, you have to concatenate the Address and the PIN fields. We’ve got you the query for that as well. Check one below.

SELECT 
    CONCAT(empl_first_name, ' ', empl_middle_name, ' ', empl_last_name) Fullname,
    CONCAT(empl_address, ' ', empl_pin) Fulladdress
FROM
    EMPL;

Once you run this query, you will see the list of all employees with their full names and addresses. Here comes the final output:

Amit Kumar Singh	Sec-62, Noida 201301
Jyoti Rani Saini	Sec-21, Noida 201301
Vimal Kumar Sood	Sec-11, Noida 201301
Neelam Jai Singh	Sec-23, Noida 201301

We hope that after wrapping up this tutorial, you should feel comfortable in using the MySQL CONCAT() method. However, you may practice more with examples to gain confidence.

Also, to learn SQL from scratch to depth, do read our step by step MySQL tutorial.