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
- What is concatenation?
- MySQL CONCAT() syntax
- MySQL CONCAT examples
- MySQL CONCAT_WS as a special form of CONCAT
- Conclusion
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';
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;
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;
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.
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:
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:
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;
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.
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;
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;
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.
