The MySQL CONCAT function is widely used to concatenate values from multiple columns into a single column. In this article, we’ll discuss the MySQL CONCAT() syntax, show you how to use it, and explore the differences between CONCAT and CONCAT_WS.
Contents
What is concatenation? MySQL
- CONCAT() syntax MySQL
-
- CONCAT Examples Select and concatenate data
- with WHERE
Using CONCAT
- CONCAT_WS as a special form of CONCAT
- Conclusion
- is concatenation?
MySQL
What
Concatenation relates not only to computer programming, where it represents the process of joining strings from end to end. In MySQL, the CONCAT function takes strings from different cells and joins them into one cell, thus creating a new string.
Why is it necessary to dominate? Suppose you need to select data from multiple columns in a large table. It aims to retrieve data and present it in a format suitable for reading. CONCAT will allow you to do this job with a simple SQL statement. In addition, it saves you space, as several chains become an element to tackle.
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 exclusive 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 precise.
The syntax of the CONCAT function is as follows:
CONCAT(expression_1,expression_2,… expression_n);
Where expression_1, expression_2, expression_n represent the strings you are combining.
You can add as many expressions as you need. However, you cannot run CONCAT on MySQL without providing at least one expression. In this case, the query will throw an error.
MySQL CONCAT
with SELECT
We usually 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 also provide a name for a new column that will display the result:
SELECT CONCAT(‘My’,’SQL’) AS ‘Result’;
The above syntax concatenates unspaced strings and only one word is obtained. If you want a ‘phrase’, you need to add spaces that divide separate words in the sentence:
SELECT CONCAT(‘MySQL ‘,’is ‘,’RDBMS’) AS Result;
Important! The concatenated string returned can be from different types of MySQL data – it depends on the arguments processed by the function:
If it has a numeric argument, CONCAT
- will convert it to its equivalent
- you have at least one binary string as an argument, CONCAT will return a binary string. If
- all arguments in the syntax are nonbinary strings, CONCAT will return a non-binary string.
- If it has NULL among its arguments, CONCAT will return NULL.
nonbinary string form. If
CONCAT() with values
NULL
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’s review the different CONCAT use cases
in practice.
MySQL CONCAT Examples
Well, we checked the concatenation of several pieces of text in a string. It’s a decent example to illustrate the way the feature works. However, work routines usually involve manipulating much larger ropes.
How to concatenate multiple columns in MySQL
Suppose we want to retrieve specific data from the customer table: the names and addresses of customers, including the cities and countries in which they live. The customer table stores all this information, as well as some other data in separate columns. We want this data to be presented in two columns. One column should contain the names of customers, and the second column should show their full addresses.
To accomplish the task, we execute the following query:
SELECT c.lastname AS Customer, CONCAT(c.address, ‘ ‘, c.city, ‘ ‘, c.country) AS Address FROM clients c;
The resulting table contains two columns: Customer and Address, where the Address column combines data from the Address, City, and Country columns.
Separate the strings to be combined in the Direction column by commas. It is optional, however, to make the data output neat and convenient for reading, we recommend using delimiters, such as spaces or commas.
The syntax example above tries to select and merge all records in the required column (our customer 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 clients LIMIT 5;
In this way, the MySQL CONCAT string will return only 5 rows:
What if you need to recover only those data that meet certain criteria? Let’s see how we can filter our results.
How to use CONCAT with WHERE
Let’s go back to our previous case. We retrieve customer data and use CONCAT to convert the values in the address, city, and country columns into a longer address chain containing all those values. Now, we need a narrower selection. We want the results to be set to show only users in Singapore.
To do this, we execute the following query:
SELECT lastname AS Customer, CONCAT(Address, ‘, ‘, postalcode, ‘, ‘, city) AS Address FROM clients WHERE country=’Singapore’;
The output is as follows
: <img src="https://blog.devart.com/wp-content/uploads/2022/08/select-concat-customers-1.png" alt="Using CONCAT with
MySQL CONCAT vs CONCAT_WS: What’s the difference?
In previous examples, we separated the parts in the resulting concatenated string with commas and spaces. However, you can also use other separators. To do this, you can add separators as arguments. But this practice is quite inconvenient and tedious. 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 in the syntax after each argument, you can only specify it once.
The syntax of CONCAT_WS() in MySQL is as follows:
CONCAT_WS(separator, expression1, expression2, expression3,…)
A separator is always specified in the first argument, and can be a string like the rest of the arguments. The function will apply the specified separator to the entire chain.
SELECT CONCAT_WS(‘ ‘, first name, last name) AS Customer_name, CONCAT_WS(‘, ‘, Address, city, country) AS Address OF customers;
=”https://blog.devart.com/wp-content/uploads/2022/08/select-concat-customers-1.png” alt=”MySQL CONCAT_WS” />
A distinguishing feature of MySQL CONCAT_WS is how it handles NULLs. As you may recall, CONCAT returns NULL if any argument is NULL.
The CONCAT_WS function works differently. 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, postal code, city) AS Address OF customers;
The query returns only NULL values.
Now, let’s add NULL as one of the arguments and a comma as a separator and verify the result.
SELECT lastname AS Customer_name, CONCAT_WS(‘, ‘, Address, NULL, city, country) AS Address FROM customers;
The function returned a valid result set despite the presence of NULL between the arguments. That’s why using CONCAT_WS is preferable if you want to display strings with separators and don’t want to worry about accidental NULLs.
NoteIf you want to concatenate values from different rows (for example, get the list of all cities in all the countries your customers come from), you must 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 is).
SELECT GROUP_CONCAT(country) OF customers;
Conclusion
The MySQL function CONCAT() and its CONCAT_WS ‘close relative’ are useful whenever we need to compile a new string from several other strings and make it easy to read. The syntax and use of functions are simple, and mastering them does not require much time and effort.
Today, modern tools and IDEs for MySQL specialists make it possible to develop SQL in visual, simple and efficient mode. We invite you to try one of the best IDEs 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 offers during a 30-day free trial.
