MySQL 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.
| 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 Version: 8.0
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.
SELECT CONCAT(pub_city,'--> ',country) FROM publisher; 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’
SELECT CONCAT(pub_city,'--> ',country) FROM publisher WHERE CONCAT(pub_name,' ',country_office)="Ultra Press Inc. London"; 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.
SELECT CONCAT(book_name,'--> ',pub_lang) FROM book_mast; 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.
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%'; 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.
1.
SELECT CONCAT(pub_city,’ — ‘,pub_name)
FROM publisher
GROUP BY pub_city;
2.
SELECT CONCAT(‘City:- ‘,pub_city,’ Publisher Name :- ‘,pub_name,’ Country Office :- ‘,country_office)
FROM publisher
WHERE pub_city=country_office
GROUP BY pub_city;
3.
SELECT CONCAT(‘Publisher Name :- ‘,pub_name,
‘Country Office:- ‘,country_office,
‘Highest Branches:- ‘,Max(no_of_branch))
FROM publisher
GROUP BY pub_name
HAVING Max(no_of_branch)>=15;
Want to practice more MySQL exercises? Click here to get three hundred plus exercises with solutions.
Video Presentation:
Your browser does not support HTML5 video.
Previous: CONCAT_WS
Next: ELT
Follow us on Facebook and Twitter for latest update.
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook
How to Concatenate Column Values In MySQL Using PHP
In this article, we show how to concatenate column values in MySQL using PHP.
Concatenating column values means that you get values from multiple columns in one MySQL statement and can combine these values to do anything. This could mean you could form a statement based on the column values. Or you could do arithmetic such as add column values together or multiply them or anything. MySQL has a built-in function Concat() that allows you to concatenate any column values together and you can do whatever you want with those values.
Below we’ll show an actual example of MySQL concatenation of multiple columns so that you can see the use.
So the MySQL code to copy a table definition is shown below.
So the above is the MySQL concatenate a column named name and a column named occupation. In between these 2 column values, we insert a string ‘ works as a ‘. Therefore, we output the person’s name, then the ‘ works as a ‘ string, and then the occupation. So if one of the rows is Janice and her occupation is a software engineer, the full line will be, Janice works as a software engineer. And we’ve just concatenate 2 column values together.
We’ll now show how to tie this in with PHP.
PHP Code
The PHP code to concatenate multiple column values is shown below.
So the following code gets the data required to make a connection to a database and actually selects a database.
We then create a variable named $result that concatenates 2 columns. The columns are name and occupation. So the code concatenates these 2 column values together and saves this concatenation as if a new row in the fulldetail row. So these 2 concatenated values are now stored together in the fulldetail row. And these columns are obtained from the Employees table. With this concatenation, we add a string to the concatenation so that it reads as if a sentence. So if a row is composed of the name Janice, who works as a software engineer, the full sentence will read, «Janice works as a software engineer».
We then use a while loop so that we can read out all the rows in the table like this. The while loop fetches all the rows in the table and stores them in the $row variable.
We create a variable named $sentence that gets the rows of fulldetail, which is the concatenated string.
We then echo out the variable sentence.
If you want to see this exact example, see below.
Example of MySQL Concatenation Using PHP
So we created a table called Employees, which you can see below. It has 4 columns: ID, name, age, and occupation.
We use the following PHP code below.
So everything is the same, only now we concatenate 3 column values together, from the name column, the age column, and the occupation columns. We insert strings into the concatenated sentence.
Below is the output from the PHP code based on the above table.