How to Concatenate Strings in MySQL

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 usage of this method with the help of simple examples.

Understand String Concatenation in MySQL

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, the 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 sections one by one.

MySQL CONCAT() Function

As stated initially, CONCAT() is a built-in MySQL function that accepts one more quoted string 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.

CONCAT() Examples

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

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

After execution, it gives the following result:

Python MySQL

If we supply a NULL value, the CONCAT() function will 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 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 by 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 the below output.

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

Similarly, if you wish to display the complete address, 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 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

Check Out – How to Concate Strings in an SQL Query

Before You Leave

We hope you can now confidently use the MySQL CONCAT() method. However, you have to practice more with examples to gain confidence.

Next, you must go through our step-by-step MySQL tutorial for learning SQL from scratch to depth.

Lastly, our site needs your support to remain free. Share this post on social media (Facebook/Twitter) if you gained some knowledge from this tutorial.

Enjoy learning,
TechBeamers.