w3resource
MYSQL Tutorial

MySQL CONCAT_WS() function

CONCAT_WS() function

MySQL CONCAT_WS() function is used to join two or more strings with a separator. The separator specified in the first argument is added between two strings. The separator itself can be a string. If the separator is NULL the result is NULL.

Syntax:

CONCAT_WS (separator, string1, string2,…)

Arguments

Name Description
separator Specifies a separator added between the strings while joining.
string1 First string to be joined.
string2 Second string to be joined. Up to N number of strings can be specified in this way.

Syntax Diagram:

MySQL CONCAT_WS() Function - Syntax Diagram

MySQL Version: 5.6

Video Presentation

Pictorial Presentation

MySQL CONCAT_WS() pictorial presentation

Example of MySQL CONCAT_WS() function

The following MySQL statement adds the first argument and second argument with a separator ", ".

Code:

SELECT CONCAT_WS(',','1st string','2nd string');

Sample Output:

mysql> SELECT CONCAT_WS(',','1st string','2nd string');
+------------------------------------------+
| CONCAT_WS(',','1st string','2nd string') |
+------------------------------------------+
| 1st string,2nd string                    | 
+------------------------------------------+
1 row in set (0.00 sec)

Example of MySQL CONCAT-WS() function with where clause

The following MySQL statement adds arguments (i.e. aut_id, aut_name, country and home_city) with a separator ",", if country of the author is not USA.

Code:

SELECT CONCAT_WS(',',aut_id,aut_name,country,home_city) 
FROM author 
WHERE country<>'USA'; 

Sample table: author


Sample Output:

mysql> SELECT CONCAT_WS(',',aut_id,aut_name,country,home_city) 
    -> FROM author 
    -> WHERE country<>'USA';
+--------------------------------------------------+
| CONCAT_WS(',',aut_id,aut_name,country,home_city) |
+--------------------------------------------------+
| AUT001,William Norton,UK,Cambridge               | 
| AUT002,William Maugham,Canada,Toronto            | 
| AUT003,William Anthony,UK,Leeds                  | 
| AUT004,S.B.Swaminathan,India,Bangalore           | 
| AUT005,Thomas Morgan,Germany,Arnsberg            | 
| AUT007,Piers Gibson,UK,London                    | 
| AUT009,Marquis de Ellis,Brazil,Rio De Janerio    | 
| AUT011,John Betjeman Hunter,Australia,Sydney     | 
| AUT012,Evan Hayek,Canada,Vancouver               | 
| AUT013,E. Howard,Australia,Adelaide              | 
| AUT014,C. J. Wilde,UK,London                     | 
+--------------------------------------------------+
11 rows in set (0.00 sec)

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_ws-function - php mysql examples | w3resource</title>
<meta name="description" content="example-concat_ws-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 authors id, name, country, home city:</h2>
<table class='table table-bordered'>
<tr>
<th>Author's id, name, country, home 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_WS(",",aut_id,aut_name,country,home_city) as output
FROM  author
WHERE country<>"USA"') as $row) {
echo "<tr>";
echo "<td>" . $row['output'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

JSP script

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-concat_ws-function</title>
</head>
<body>
<%
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_WS(',',aut_id,aut_name,country,home_city) as output FROM  author WHERE country<>'USA'";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Author's id, name, country, home city</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("output")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Cant connect to database.");
}
%>
</body>
</html>

Difference between MySQL GROUP_CONCAT() and CONCAT_WS()

GROUP_CONCAT() function returns a string with concatenated non-NULL value from a group.
CONCAT_WS() function is used to add two or more strings with separator

See the following example:

mysql> SELECT userid, fname, lname  FROM user_details;
+----------+--------+---------+
| userid   | fname  | lname   |
+----------+--------+---------+
| scott123 | Scott  | Rayy    |
| ferp6734 | Palash | Ghosh   |
| diana094 | Diana  | Lorentz |
| abcd123  | John   | ray     |
+----------+--------+---------+
4 rows in set (0.00 sec)
mysql> SELECT GROUP_CONCAT(CONCAT_WS(' ', fname,lname)) FROM user_details ORDER BY userid ASC;
+------------------------------------------------+
| GROUP_CONCAT(CONCAT_WS(' ', fname,lname))      |
+------------------------------------------------+
| Scott Rayy,Palash Ghosh,Diana Lorentz,John ray |
+------------------------------------------------+
1 row in set (0.03 sec)

Here CONCAT_WS() insert a space as a separator between fname and lname while GROUP_CONCAT() returns the result in a single string.

All String Functions

MySQL String Functions, slide presentation

Many of our valued users post comments along with piece of code. Disqus may, false positively, consider those piece of code as Bad or Starnge syntax and send those comments to spam automatically. Even if that happens, we will make sure those useful comments are taken out of spam and approved. It may take a couple of days for that though, but any useful comment will be brought to public view for sure. We regret if that happend to any user. You may write us directly regarding this to - w3resource[at]yahoo[dot]com.