MySQL | CONCAT_WS method
Start your free 7-days trial now!
MySQL's CONCAT_WS(~) method concatenates input strings using the specified separator.
Parameters
1. separator | string
The separator to be added between the strings to be concatenated.
2. str1 | string
A string to concatenate with other string inputs.
3. str2 | string
A string to concatenate with other string inputs. Any number of string inputs may be provided.
Return value
A concatenated string with the separator added between the string inputs.
Examples
Consider the following table about some students:
student_id | fname | lname | day_enrolled | age | username |
|---|---|---|---|---|---|
1 | Sky | Towner | 2015-12-03 | 17 | stowner1 |
2 | Ben | Davis | 2016-04-20 | 19 | bdavis2 |
3 | Travis | Apple | 2018-08-14 | 18 | tapple3 |
4 | Arthur | David | 2016-04-01 | 16 | adavid4 |
5 | Benjamin | Town | 2014-01-01 | 17 | btown5 |
The above sample table can be created using the code here.
Basic usage
To concatenate fname and lname with a '*' in between:
SELECT CONCAT_WS('*', fname, lname)FROM students;
+------------------------------+| CONCAT_WS('*', fname, lname) |+------------------------------+| Sky*Towner || Ben*Davis || Travis*Apple || Arthur*David || Benjamin*Town |+------------------------------+
To provide an alias for the resulting column using AS:
SELECT CONCAT_WS(' ', fname, lname) AS "Full Name"FROM students;
+---------------+| Full Name |+---------------+| Sky Towner || Ben Davis || Travis Apple || Arthur David || Benjamin Town |+---------------+
Numeric arguments
Numeric arguments are automatically converted to strings:
SELECT CONCAT_WS(' ', fname, 'is', age, 'years old.') AS "Sentence"FROM students;
+---------------------------+| Sentence |+---------------------------+| Sky is 17 years old. || Ben is 19 years old. || Travis is 18 years old. || Arthur is 16 years old. || Benjamin is 17 years old. |+---------------------------+
NULL arguments
NULL input strings are skipped:
SELECT CONCAT_WS('-', 'Welcome', NULL, 'to', 'SkyTowner!');
+-----------------------------------------------------+| CONCAT_WS('-', 'Welcome', NULL, 'to', 'SkyTowner!') |+-----------------------------------------------------+| Welcome-to-SkyTowner! |+-----------------------------------------------------+
NULL separator returns NULL:
SELECT CONCAT_WS(NULL, 'Welcome', 'to', 'SkyTowner!');
+------------------------------------------------+| CONCAT_WS(NULL, 'Welcome', 'to', 'SkyTowner!') |+------------------------------------------------+| NULL |+------------------------------------------------+
