MySQL Concat Function | Join Strings in MySQL

In this article, I will talk about the concatenation of two or more strings in MySQL using MySQL CONCAT Function.

Concatenation, in the context of programming, is the operation of joining two strings together. The term”concatenation” literally means to merge two things together.

https://techopedia.com

MySQL provides two functions that concatenate string values: CONCAT and CONCAT_WS.

Table Structure

To follow the tutorial along, create the following table in your database and insert some sample data. Although I will be providing outputs along the queries in my post, it is recommended that you follow the tutorial along by running the same queries.

CREATE TABLE Users (
id int(6) unsigned NOT NULL AUTO_INCREMENT,
firstname varchar(30) NOT NULL,
lastname varchar(30) DEFAULT NULL,
email varchar(50) DEFAULT NULL,
date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1ULT CHARSET=latin1;

insert into Users (firstname, lastname, email) VALUES ('Vishesh', 'Ahuja', '[email protected]');

insert into Users (firstname, lastname, email) VALUES ('John', 'Smith', '[email protected]');

insert into Users (firstname, lastname, email) VALUES ('Emma', 'Jones', '[email protected]');

MySQL CONCAT Function

MySQL CONCAT Function requires one or more string as parameters and it concatenates them into a single string. If you don’t provide any parameters in the string, then it raises an error. Use the following query syntax to join two or more columns in the table:

mysql> SELECT CONCAT(firstname, lastname) FROM Users;
+-----------------------------+
| CONCAT(firstname, lastname) |
+-----------------------------+
| VisheshAhuja |
| JohnSmith |
| EmmaJones |
+-----------------------------+

SELECT CONCAT(string1,string2, … ) FROM table_name;

You can create an alias for the concatenated string and use it directly in your backend language (like PHP, Java etc). In order to add spaces or any other separator, simply concatenate it as a string as illustrated in the next example:

mysql> SELECT CONCAT(firstname, ' ', lastname) as fullname, email FROM Users;
+---------------+------------------------+
| fullname | email |
+---------------+------------------------+
| Vishesh Ahuja | [email protected] |
| John Smith | [email protected] |
| Emma Jones | [email protected] |
+---------------+------------------------+

Be Careful With NULL Values

The disclaimer worth mentioning with MySQL Concat Function is that you should be careful with NULL Values while using it. If there is any NULL value in one of the columns for any row then Concat returns NULL. To demonstrate it, insert a new row with NULL lastname in your table.

insert into Users (firstname, lastname, email) VALUES ('Ross', NULL, '[email protected]');

SELECT CONCAT(firstname, lastname) as fullName, email FROM Users;
+--------------+------------------------+
| fullName | email |
+--------------+------------------------+
| VisheshAhuja | [email protected] |
| JohnSmith | [email protected] |
| EmmaJones | [email protected] |
| NULL | [email protected] |
+--------------+------------------------+

Notice in the above example, the fourth row in the output of the query has fullName as NULL.

Resolve NULL Value Issue: CONCAT_WS

In order to resolve the NULL value issue, you can use the other function with MySQL provides for string concatenation: CONCAT_WS. The CONCAT_WS Function concatenates strings together with a predefined separator. The syntax for using CONCAT_WS is as follows:

SELECT CONCAT_WS(seperator,string1,string2, ... ) from table_name;

The first argument is the predefined separator for other arguments. CONCAT_WS adds the separator between other strings and returns a full string just like CONCAT function. However, it also skips the NULL values which will resolve the NULL value issue with CONCAT function.

mysql> SELECT CONCAT_WS(" ", firstname, lastname) as fullName, email FROM Users;
+---------------+------------------------+
| fullName | email |
+---------------+------------------------+
| Vishesh Ahuja | [email protected] |
| John Smith | [email protected] |
| Emma Jones | [email protected] |
| Ross | [email protected] |
+---------------+------------------------+

Conclusion

In this article you have learned how to use MySQL CONCAT and CONCAT_WS function. CONCAT function joins two or more strings together, while CONCAT_WS joins strings with a predefined separator.

You can learn more about MySQL String functions on the Official MySQL Documentation. To read more blog posts on MySQL on Concatly! Kindly give your valuable comments to help me improve this resource.

Spread the Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *