Saturday, December 21, 2024
HomeHow ToMySQL CONCAT Function: How to Concatenate Strings in MySQL

MySQL CONCAT Function: How to Concatenate Strings in MySQL

The MySQL CONCAT function is widely used to concatenate multiple column values into a single column. In this article, we will look into the MySQL CONCAT() syntax, demonstrate to you how to use it, and explore the differences between CONCAT and CONCAT_WS.

Contents

Download dbForge Studio for MySQL

What is concatenation?

Concatenation relates not only to computer programming, where it stands for the process of joining character strings end-to-end. In MySQL, the CONCAT function takes strings from different cells and joins them together in one cell, thus creating a new string.

Why is it necessary to master? Assume you need to perform the data selection from several columns of a large table. Your goal is to retrieve data and present it in a format suitable for reading. CONCAT will let you do this job with one straightforward SQL statement. Besides, it saves you space, as several strings become one item to address.

This article will explore the syntax and usage of the CONCAT function in MySQL to help you understand and master it excellently. 

MySQL CONCAT() function

The CONCAT function is not unique to MySQL databases. It is a regular SQL function also present in Oracle, SQL Server, and PostgreSQL. However, in MySQL, the syntax is simpler and more accurate. 

The syntax for the CONCAT function is as follows:

CONCAT(expression_1,expression_2,...expression_n); 

Where expression_1, expression_2, expression_n stand for the strings you are combining. 

You can add as many expressions as you need. However, you can’t execute CONCAT in MySQL without providing at least one expression. In this case, the query will throw an error. 

MySQL CONCAT with SELECT

Usually, we use CONCAT() with the SELECT statement. The simplest example is joining different pieces of text together to combine a new word or phrase. Note that in the following example we are also providing a name for a new column that will display the result:

SELECT CONCAT('My','SQL') AS 'Result';  
MySQL CONCAT() syntax

The above syntax concatenates strings without a space, and you get only one word. If you want a ‘phrase,’ you need to add spaces dividing separate words in the phrase:

SELECT CONCAT('MySQL ','is ','RDBMS') AS Result; 
MySQL CONCAT() syntax with separators

Important! The returned concatenated string may be of different MySQL data types – it depends on the arguments processed by the function:

  • If you have a numeric argument, CONCAT will convert it to its equivalent nonbinary string form.
  • If you have at least one binary string as an argument, CONCAT will return a binary string. 
  • If all the arguments in the syntax are nonbinary strings, CONCAT will return a nonbinary string. 
  • If you have NULL among your arguments, CONCAT will return NULL.

CONCAT() with NULL values

Let’s look at the example where one of the arguments is NULL. Because of that, the function returns NULL:

SELECT CONCAT('First Name, ','Last Name, ',NULL,'Address') AS Result;  
MySQL CONCAT() NULLs

Now, let us review the different cases of using CONCAT in practice.

MySQL CONCAT examples

Well, we reviewed the concatenation of several text fragments into one string. It is a decent example to illustrate the way the function works. However, work routines usually involve manipulating much larger strings.

How to concatenate multiple columns in MySQL

Suppose, we want to retrieve specific data from the customers table – the names and addresses of customers, including the cities and countries they live in. The customers table stores all this information as well as some other data in separate columns. We want these data to be presented in two columns. One column should contain the customers’ names, and the second one should display their full addresses. 

To achieve the task, we execute the following query:

SELECT
   c.lastname AS Customer,
   CONCAT(c.address, ' ', c.city, ' ', c.country) AS Address
 FROM customers c;

The resulting table contains two columns – Customer and Address, where the Address column combines data from the address, city, and country columns.

Select and concatenate data

We separated the strings to be combined in the Address column by commas. It is optional, however, for the data output to be neat and convenient for reading, we recommend using delimiters, such as spaces or commas.

The above syntax example deals with selecting and combining all records from the required column (our customers table has 100 rows). If your MySQL table is much larger, and you don’t want to retrieve all the data, you can always limit the number of results. CONCAT LIMIT solves the task:

SELECT
   lastname AS Customer,
   CONCAT(Address, ', ', postalcode, ', ', city) AS Address
 FROM customers
 LIMIT 5;

This way, the MySQL CONCAT string will return 5 rows only: 

MySQL select and concatenate data

What if you need to retrieve only those data that meets certain criteria? Let’s see how we can filter our results.

How to use CONCAT with WHERE

Let’s get back to our previous case. We retrieved the customers’ data and used CONCAT to turn the values from the address, city, and country columns into a longer address string containing all those values. Now, we need a narrower selection. We want the results set to display only users from Singapore. 

For this, we execute the following query:

SELECT
   lastname AS Customer,
   CONCAT(Address, ', ', postalcode, ', ', city) AS Address
 FROM customers
 WHERE country='Singapore';

The result is as follows:

Use CONCAT with WHERE

MySQL CONCAT vs CONCAT_WS: What is the difference?

In previous examples, we separated the parts in the resulting concatenated string with commas and spaces. However, you can use other separators as well. For this, you can add separators as arguments. But this practice is quite inconvenient and tiresome. Fortunately, there is another option. 

CONCAT_WS() comprises a variation of the CONCAT() function and stands for Concatenate With Separator. Instead of including the separator into the syntax after each argument, you can specify it only once.

The syntax for CONCAT_WS() in MySQL is as follows:

CONCAT_WS(separator, expression1, expression2, expression3,...) 

A separator is always specified in the first argument, and it can be a string as the rest of the arguments. The function will apply the specified separator to the entire string.

SELECT
   CONCAT_WS(' ', firstname, lastname) AS Customer_name,
   CONCAT_WS(', ', Address, city, country) AS Address
 FROM customers;
MySQL CONCAT_WS

A distinctive feature of MySQL CONCAT_WS is how it treats NULLs. As you remember, CONCAT returns NULL if any argument is NULL.

The CONCAT_WS function works differently. It returns NULL if the separator is NULL. But, if any of the concatenated arguments is NULL, this function will ignore that argument while the rest of the arguments will be processed and concatenated.

SELECT
   CONCAT_WS(NULL, address, postalcode, city) AS Address
 FROM customers;

The query returns NULL values only.

The CONCAT_WS function and NULLs

Now, let’s add NULL as one of the arguments and a comma as a separator and check the result.

SELECT lastname AS Customer_name,
   CONCAT_WS(', ', Address, NULL, city, country) AS Address
 FROM customers;
MySQL CONCAT_WS as a special form of CONCAT

The function has returned a valid results set despite the presence of NULL among the arguments. That’s why using CONCAT_WS is preferable if you want to display strings with separators and don’t want to bother about accidental NULLs. 

Note
If you want to concatenate values from different rows (for example, get the list of all cities from all countries your customers come from), you should use GROUP_CONCAT(). It is a separate aggregate function in MySQL, not a CONCAT with GROUP BY (in fact, GROUP BY does not work with CONCAT as it is).

SELECT 
     GROUP_CONCAT(country)
 FROM
     customers;
GROUP_CONCAT

Conclusion 

MySQL CONCAT() function and its ‘close relative’ CONCAT_WS() come helpful whenever we need to compile a new string of several other strings and make it read-friendly. The syntax and the usage of the functions are simple, and mastering them does not require much time and effort. 

Today, modern tools and IDEs for MySQL specialists allow developing SQL in a visual mode, simply and efficiently. We invite you to test-drive one of the best IDE for MySQL on the market, a decent alternative to MySQL Workbench⁠—dbForge Studio for MySQL.

Download the Studio from our website and evaluate all the cutting-edge features it delivers during a free 30-day trial. 

Download dbForge Studio for MySQL
RELATED ARTICLES

Whitepaper

Social

Topics

Products