Hướng dẫn mysql combine string fields

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.

Syntax:

CONCAT (string1, string2,…)

Arguments:

Syntax Diagram:

MySQL Version: 5.6

Pictorial representation of MySQL CONCAT() function

Video Presentation:

Your browser does not support HTML5 video.

Examples: MySQL Concat() Function

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

Sample 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

Sample 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

Sample 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

Sample 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)

PHP Script:

list of publisher's city and country with 'publisher's city--->country' format:

query('SELECT CONCAT(pub_city,"--> ",country)
FROM publisher') as $row) {
echo "";
echo "";
echo "";
}
?>


Publisher's City
" . $row['CONCAT(pub_city,"--> ",country)'] . "

JSP Script

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>







<%
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data ="SELECT CONCAT(pub_city,'--> ',country) FROM publisher";
rs = statement.executeQuery(Data);
%>

<%
while (rs.next()) {
%>

<%   }    %>


Publisher's City

<%=rs.getString("CONCAT(pub_city,'--> ',country)")%>

<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Cant connect to database.");
}
%>

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

MySQL Online Editor