w3resource

MySQL CONCAT() function

CONCAT() function

MySQL CONCAT() function is used to add two or more strings.

  • There may be one or more arguments.
  • Returns the string that results from concatenating the arguments.
  • Returns a nonbinary string, if all arguments are nonbinary strings.
  • Returns a binary string, if the arguments include any binary strings.
  • If the argument is numeric, it is converted to its equivalent nonbinary string form.
  • Returns NULL if any argument is NULL.

This function is useful in -

  • String concatenation: It allows us to join multiple strings together into a single string.
  • Data formatting: CONCAT() can be used to format and present data in a desired format.
  • Expression evaluation: The CONCAT() function can also be used to concatenate the result of expressions or functions.

Syntax:

CONCAT (string1, string2,…)

Arguments:

Name Description
string1 First string to be joined.
string2 Second string to be joined. Up to N number of strings can be specified this way.

Syntax Diagram:

MySQL CONCAT() Function - Syntax Diagram

MySQL Version: 8.0

MySQL: CONCAT() Function - w3resource

Pictorial representation of MySQL CONCAT() function

pictorial representation of MySQL CONCAT function

 

Examples: MySQL Concat() Function

Table of Contents :

Example of MySQL Concat Function using agruments

One argument:

mysql> SELECT CONCAT('w3resource');
+----------------------+
| CONCAT('w3resource') |
+----------------------+
| w3resource           |
+----------------------+
1 row in set (0.00 sec)

Two or more arguments:

mysql> SELECT CONCAT('w3resource','.','com');
+--------------------------------+
| CONCAT('w3resource','.','com') |
+--------------------------------+
| w3resource.com                 |
+--------------------------------+
1 row in set (0.00 sec)

One of the arguments is NULL :

mysql> SELECT CONCAT('w3resource','.','com',NULL);
+-------------------------------------+
| CONCAT('w3resource','.','com',NULL) |
+-------------------------------------+
| NULL                                |
+-------------------------------------+
1 row in set (0.02 sec)

Numeric argument:

mysql> SELECT CONCAT(102.33);
+----------------+
| CONCAT(102.33) |
+----------------+
| 102.33         |
+----------------+
1 row in set (0.00 sec)

For quoted strings, concatenation can be performed by placing the strings next to each other :

mysql> SELECT 'w3resource' '.' 'com';
+----------------+
| w3resource     |
+----------------+
| w3resource.com | 
+----------------+
1 row in set (0.00 sec)

Example of MySQL CONCAT() function on columns

The following MySQL statement will add values of pub_city column with values of the country column of publisher table placing a '-->' between them.

Code:

SELECT CONCAT(pub_city,'--> ',country)
FROM publisher; 

Sample table: publisher


Output:

mysql> SELECT CONCAT(pub_city,'--> ',country)
    -> FROM publisher;
+---------------------------------+
| CONCAT(pub_city,'--> ',country) |
+---------------------------------+
| New York--> USA                 | 
| Mumbai--> India                 | 
| Adelaide--> Australia           | 
| London--> UK                    | 
| Houstan--> USA                  | 
| New York--> USA                 | 
| Cambridge--> UK                 | 
| New Delhi--> India              | 
+---------------------------------+
8 rows in set (0.00 sec)

MySQL CONCAT using WHERE clause

The following MySQL statement will add pub_city and country column by a '-->' for those publishers whose concatinated name and country office is 'Ultra Press Inc. London'

Code:

SELECT CONCAT(pub_city,'--> ',country)
FROM publisher
WHERE CONCAT(pub_name,' ',country_office)="Ultra Press Inc. London"; 

Sample table: publisher


Output:

mysql> SELECT CONCAT(pub_city,'--> ',country)
    -> FROM publisher
    -> WHERE CONCAT(pub_name,' ',country_office)="Ultra Press Inc. London";
+---------------------------------+
| CONCAT(pub_city,'--> ',country) |
+---------------------------------+
| London--> UK                    |
+---------------------------------+
1 row in set (0.02 sec)

MySQL CONCAT returns NULL if any field contain NULL

The following MySQL statement will add book name and pub_lang column by a '-->' for all the books.

Code:

SELECT CONCAT(book_name,'--> ',pub_lang)
FROM book_mast;

Sample table: book_mast


Output:

mysql> SELECT CONCAT(book_name,'--> ',pub_lang)
    -> FROM book_mast;
+------------------------------------------------+
| CONCAT(book_name,'--> ',pub_lang)              |
+------------------------------------------------+
| Introduction to Electrodynamics--> English     |
| Understanding of Steel Construction--> English |
| Guide to Networking--> Hindi                   |
| Transfer  of Heat and Mass--> English          |
| NULL                                           |
| Fundamentals of Heat--> German                 |
| Advanced 3d Graphics--> Hindi                  |
| Human Anatomy--> German                        |
| Mental Health Nursing--> English               |
| Fundamentals of Thermodynamics--> English      |
| The Experimental Analysis of Cat--> French     |
| The Nature  of World--> English                |
| Environment a Sustainable Future--> German     |
| NULL                                           |
| Anatomy & Physiology--> Hindi                  |
| Networks and Telecommunications--> French      |
+------------------------------------------------+
16 rows in set (0.01 sec)

The above output shows that, when the value of any of the two columns mention above is NULL, the output returns NULL, mention by red color.

MySQL CONCAT using JOINS and wildcard character

The following MySQL statement will show the combination of first name and last name and job title for those employees who contains the word Smith to their first and last name combination.

Code:

SELECT CONCAT( first_name,  ' ', last_name ) AS  "name", job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id
AND CONCAT( first_name,  ' ', last_name ) LIKE  '%Smith%';

Sample table: employees


Sample table: jobs


Output:

mysql> SELECT CONCAT( first_name,  ' ', last_name ) AS  "name", job_title
    -> FROM employees e, jobs j
    -> WHERE e.job_id = j.job_id
    -> AND CONCAT( first_name,  ' ', last_name ) LIKE  '%Smith%';
+---------------+----------------------+
| name          | job_title            |
+---------------+----------------------+
| Lindsey Smith | Sales Representative |
| William Smith | Sales Representative |
+---------------+----------------------+
2 rows in set (0.00 sec)

Try the following Queries

Write a SQL statement to display the publisher city and name according to the group on publisher city.

Write a SQL statement to display the publisher city and name and country office with a suitable title for those publishers which country office and publishing city are in the same place.

Write a SQL statement to display the publisher name, country office and a maximum number of branches with the suitable title for those publishers who maintain on and above 15 branches worldwide.

Sample table : publisher

 Want to practice more MySQL exercises? Click here to get three hundred plus exercises with solutions.

Video Presentation:

All String Functions (Slides presentation)

Previous: CONCAT_WS
Next: ELT



Follow us on Facebook and Twitter for latest update.