w3resource

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.

This function is useful in -

  • String concatenation: It allows you to concatenate multiple strings together into a single string.
  • Handling empty or NULL values: CONCAT_WS() omits empty or NULL values and does not add the separator.

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: 8.0

MySQL: CONCAT_WS() Function - w3resource

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');

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


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)

MySQL CONCAT_WS(): Handling NULL Values

The following MySQL statement concatenate multiple values along with NULL. If any NULL value found the NULL values are automatically skipped.

Code:

SELECT CONCAT_WS(' - ', 'Apple', NULL, 'Orange');

Output:

CONCAT_WS(' - ', 'Apple', NULL, 'Orange')|
-----------------------------------------+
Apple - Orange                           |

MySQL CONCAT_WS(): Combining Columns with NULL as Default Value

The following MySQL statement demonstrate how to use the CONCAT_WS() function to concatenate strings together with a specified separator, handle NULL values, and combine columns in various scenarios.

Code:

SELECT CONCAT_WS(' ', first_name, COALESCE(middle_name, 'N/A'), last_name) AS full_name 
FROM emp_test;

Sample table: emp_test


Output:

full_name   |
------------+
Jhon De Wood|
Piter   Van |
Monti       |
Mitchel N/A |

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.

Video Presentation:

All String Functions (Slides presentation)

Previous: CHARACTER_LENGTH
Next: CONCAT



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/mysql/string-functions/mysql-concat_ws-function.php