CONCAT() Function – MySQL

CONCAT() Function – MySQL

MySQL has the CONCAT() function, which allows you to concatenate two or more strings. The function actually allows for one or more arguments, but its main use is to concatenate two or more strings.

CONCAT (string1, string2,…)

Arguments

string1 First string to be joined.
string2 Second string to be joined. Up to N number of strings can be specified this way.
Here’s an example:

SELECT CONCAT(‘Homer’, ‘ ‘, ‘Simpson’) AS ‘Full Name’;
Result:

+—————+
| Full Name |
+—————+
| Homer Simpson |
+—————+

SELECT CONCAT(‘Homer’, ‘Simpson’) AS ‘Full Name’;
Result:

+————–+
| Full Name |
+————–+
| HomerSimpson |
+————–+
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> 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)
NULL Arguments
The CONCAT() function returns NULL if any argument is NULL.

Example:

SELECT CONCAT(‘Homer’, NULL, ‘Simpson’) AS ‘Full Name’;
Result:

+———–+
| Full Name |
+———–+
| NULL |
+———–+

PHP usages of mysql Concatenate function

PHP Script

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-concat-function - php mysql examples | w3resource</title>
<meta name="description" content="example-concat-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>list of publisher's city and country with 'publisher's city--->country' format:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher's City</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT CONCAT(pub_city,"--> ",country)
FROM publisher') as $row) {
echo "<tr>";
echo "<td>" . $row['CONCAT(pub_city,"--> ",country)'] . "</td>";
echo "</tr>";
}
?>
</tbody>
</table>
</div>
</div>
</div>
</body>
</html>

 

 

For any query or issue, feel free to discuss on http://discuss.eduguru.in