MySQL CONCAT_WS() Functions – String Functions

by · Published · Updated

MySQL CONCAT_WS() Functions: Syntax

CONCAT_WS ( separator, string1, string2, .. stringN)

MySQL CONCAT_WS() Functions: Description

CONCAT_WS() functions returns the concatenated string and adds a separator between each of the concatenated expressions. You may have one or more arguments.

  • It does not skip empty strings.
  • If the separator is NULL, the result is NULL.
  • It skips any NULL values after the separator argument.

It is a special form of CONCAT().

MySQL CONCAT_WS() Functions: Parameter

[table caption=”” width=”100%” colwidth=”15%|30%|55%” colalign=”left|left|left”]
Name, Required /Optional, Description
separator,Required, The separator to add between each of the expressions.
string1, Required, first parameter is must.
string2 , Required, second parameter is must.
stringN, Optional, N number of strings can be passed as parameter.
[/table]

MySQL CONCAT_WS() Functions: Output

[table caption=”” width=”100%” colwidth=”20%|80%” colalign=”left|left”]
Returns,
NULL,If the separator is NULL.
concatenated string, On the basis of inputs passed
[/table]

MySQL CONCAT_WS() Functions: Available from

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Version, MySQL 4.0
[/table]

 

MySQL CONCAT_WS() Functions: Example 1

mysql> SELECT CONCAT_WS('_','Tutorial', 'mines', '.net') ;
+--------------------------------------------+
| CONCAT_WS('_','Tutorial', 'mines', '.net') |
+--------------------------------------------+
| Tutorial_mines_.net                        |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT_WS('_','Tutorial', NULL, 'mines', '.net') ; // skips any NULL values 
+--------------------------------------------------+
| CONCAT_WS('_','Tutorial', NULL, 'mines', '.net') |
+--------------------------------------------------+
| Tutorial_mines_.net |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS('xxx','Tutorial', NULL, 'mines', '.net') ;
+----------------------------------------------------+
| CONCAT_WS('xxx','Tutorial', NULL, 'mines', '.net') |
+----------------------------------------------------+
| Tutorialxxxminesxxx.net |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS('xxx','Tutorial', 'mines', '.net') ;
+----------------------------------------------+
| CONCAT_WS('xxx','Tutorial', 'mines', '.net') |
+----------------------------------------------+
| Tutorialxxxminesxxx.net |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT_WS(NULL ,'Tutorial', 'mines', '.net') ;
+----------------------------------------------+
| CONCAT_WS(NULL ,'Tutorial', 'mines', '.net') |
+----------------------------------------------+
| NULL                                         |
+----------------------------------------------+
1 row in set (0.00 sec)

Below is the mysql terminal screen for above code :

See all MySQL String functions MySQL 8 String Functions.

Related articles : LOCATE(), HEX(), CONCAT(), LOWER(), LTRIM(), INSTR(), POSITION().

PHP Related articles : IMPLODE(), SUBSTR_COUNT(), SUBSTR_COMPARE(), PHP STRING FUNCTIONS(), JOIN().

You may also like...