MySQL Combine String Columns with CONCAT Function

Concatenation of strings in MySQL helps one to add one string to the end of another. Concatenating string or databank fields into a solitary field within the result set is feasible with MySQL’s string management methods. Within this guide, while using the MySQL CONCAT or CONCAT WS methods, you will discover different ways of concatenating two or more strings simultaneously.

One or even more arguments can occur.
Concatenates the arguments and returns the resultant string.
When all values are non-binary strings, yield a non-binary string.
Yields a binary string if any binary strings are used in the arguments.
If it is numerical, it’s also translated to its non-binary string-like form.
If every argument is NULL, this function returns NULL.

Open the MySQL command-line client shell from the applications, and add your password upon asking.

Example 01: Concatenate Two Columns Using CONCAT

We have a table ‘teacher’ in the database ‘data’. We want to associate its strings from two columns “TeachName” and “lastname” without a space between them.

Execute the SELECT CONCAT command having column names in the brackets separated by a comma. The new column ‘Name’ is generated to store the concatenated string values in it and the result is shown below.

>> SELECT CONCAT(TeachName, lastname) AS Name FROM data.teacher;

Example 02: Concatenate Two Columns with Space

Suppose we have the below table “student” in the database “data” and we want to combine its strings from two columns “Name” and “Subject” with space between the values.

Use the below SELECT CONCAT command while providing the names of columns in brackets to combine the string values separated by a space. The concatenated values will be stored in a new column, “StudentDetail.” The resulted column has all the concatenated strings now.

>> SELECT CONCAT(Name, ‘ ‘, Subject) AS StudentDetail FROM data.student;

Example 03: Concatenate Multiple Columns with Special Characters

Let’s assume the below table “teacher” to concatenate string values from more than two columns with a different special character.

Try the below command while adding the ‘-‘ sign instead of space. The resulted set has the concatenated column of strings from the table columns with special characters used in it.

>> SELECT CONCAT(TeachName, ‘-‘, subject, ‘-‘, qualification) AS Detail FROM data.teacher;

Example 04: Concatenate While Retrieving Additional Columns

If you want to concatenate the column strings while fetching other columns in the same query, you are at the right place. Consider the table “animals” in the database “data” below.

We have been concatenating its three columns; “Color”, “Name”, and “Gender” while using space and special characters in between. The concatenated string from these columns will be saved into a new column, “AnimData”. On the other hand, we have been accessing the records of other columns “Price” and “Age” from this table. The records will be fetched from the rows where the gender of the animals is “M” means male-only. You have the results of concatenated strings from different columns as well as other columns that have been displayed separately.

>> SELECT CONCAT(Color, ‘ ‘, Name, ‘-‘, Gender) AS AnimData, Price, Age FROM data.animals WHERE Gender = ‘M’;

Example 05: Concatenate More Strings with Column Strings

If you want to add strings instead of special characters or spaces, you can do that too. So, let’s have a simple example of doing this. Assume you have a table “book” and you have the below data regarding books, their prices, authors, volumes, and pages as shown in the picture. Now, we will be concatenating the strings from the column “Name”, “Author”, and “Price” using this table.

We have been using the SELECT CONCAT statement to concatenate strings from the three columns. All the data of this table is firstly sorted in the ascending order of column “Name”. In the brackets, we have provided “The book”, “wrote by”, and “has price” as extra strings instead of space or special characters within inverted commas. Now the CONCAT function will take the first inverted comma value “The book” along with the value from the column “Name”, then the second inverted comma value ‘wrote by’ followed by the column “Author” string value, and lastly the third inverted comma value “has price” followed by the value of column ‘Price’. All these strings and values from the columns will be combined and it will make a fully fledge sentence. This whole new mega string sentence will be stored in the new column “BookDetail”.

>> SELECT CONCAT(‘The book ‘, Name, ‘ wrote by ‘, Author, ‘ has price ‘, Price) AS BookDetail FROM data.book ORDER BY Name ASC;

Example 06: Concatenate Column Strings Using CONCAT_WS

CONCAT_WS seems to be a unique variant of the CONCAT feature that allows you to specify which symbol (or characters) would be castoff as a divider concerning the string concatenation. It is as simple as the simple CONCAT function. So, let’s consider the table “social” in the MySQL database having values about the users, their most used social media applications, and the ages of users. Now we will perform the concatenation using the CONCAT_WS function.

In the query below, we have been concatenating three columns and storing this concatenated result into column “Detail”. As you can observe, there is something different since we have defined some special characters “***” in the inverted commas before the column names. This is because we want to add these special characters between the strings of the columns, coming one after another using the CONTACT_WS function. So, from this scenario, it is clear that we don’t need to put special characters in the query after every column specified when it comes to the same type of character.

>> SELECT CONCAT_WS(‘***‘, User, Website, Age) AS Detail FROM data.social;

Conclusion:

You are now effectively erudite of all the essential things about the concatenation of string columns and their values using the simple CONCAT function and the CONCAT_WS function in MySQL Shell.