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 Version: 8.0
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
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics