SQL - CONCAT_WS() Function

The SQL CONCAT_WS() function accepts a separator, one or more string values as parameters and, concatenates/join all the given strings placing the specified separator in between them and returns the result.

The first argument of this function is considered as the separator. It can be a string or a numerical value. If the separator value is NULL this function returns NULL.

If there are any empty stings in the string values they will be included in the result. But if you pass NULL as a string value (after the separator) it is neglected.

Syntax

Following is the syntax uses the SQL CONCAT_WS() function with two or more strings −

CONCAT_WS(separator, str1, str2, ........strN);

This syntax uses the CONCAT_WS function with multiple columns of the SQL table −

SELECT CONCAT_WS(separator, column1, column2, ……. column_NameN) AS CONCAT_WS Function FROM Table_Name;

Parameters

  • str − It accepts the string(one or more string) along with the separator.

Return value

The SQL CONCAT_WS() function returns the same string as the operator returns from the same expressions as the operands along with separator.

Example

The following SELECT query adds two characters and creates a new string −

SELECT CONCAT_WS('_','Tutorialspoint', 'Company') AS CONCAT_WSfunction;

Output

Following is the output of the above query −

+------------------------+
| CONCAT_WSfunction      |
+------------------------+
| Tutorialspoint_Company |
+------------------------+

Example

The following SELECT query returns a new string as space by adding more than 2 characters −

SELECT CONCAT_WS(' ', 'B', 'I', 'R', 'T', 'H', 'D', 'A', 'Y') AS HAPPY;

Output

The above SQL query produces the following output −

+-----------------+
| HAPPY           |
+-----------------+
| B I R T H D A Y |
+-----------------+

Example

The following Query uses space between two strings −

SELECT CONCAT_WS(' ', 'HELLO',  'WORLD') AS CONCAT_WSFunction;

Output

On executing the above query, it will produce the following output −

+-------------------+
| CONCAT_WSFunction |
+-------------------+
| HELLO WORLD       |
+-------------------+

Example

Here, we can also provide numerical values as a separator −

SELECT CONCAT_WS('456', 'Good', 'morning', 'Everyone') AS CONCAT_WSFunction;

Output

On executing the above query, it will produce the following output −

+---------------------------+
| CONCAT_WSFunction         |
+---------------------------+
| Good456morning456Everyone |
+---------------------------+

Example

The following SELECT query adds two strings that contain symbols −

SELECT CONCAT_WS(' = ', '#$%jhg*', '@#$*&^gd%786') AS CONCAT_WSFunction;

Output

On executing the above query, it will produce the following output −

+------------------------+
| CONCAT_WSFunction      |
+------------------------+
| #$%jhg* = @#$*&^gd%786 |
+------------------------+

Example

In the following example we are passing a NULL value as a separator. If you do so, as you observe the result will be NULL −

SELECT CONCAT_WS(NULL, 'HELLO', 'WORLD') AS CONCAT_WSFunction;

Output

On executing the above query, it will produce the following output −

+-------------------+
| CONCAT_WSFunction |
+-------------------+
| NULL              |
+-------------------+

Example

Now, let us try providing NULL as a string value (after the separator). If you do so it is neglected and not included in the result −

SELECT CONCAT_WS(' ', 'HELLO', NULL, 'WORLD') AS CONCAT_WSFunction;

Output

On executing the above query, it will produce the following output −

+-------------------+
| CONCAT_WSFunction |
+-------------------+
| HELLO WORLD       |
+-------------------+

Example

Following is an example where we are passing an empty string as an argument to the CONCAT() function −

SELECT CONCAT(' ');

Output

On executing the above query, it will produce the following output −

+-------------+
| CONCAT(' ') |
+-------------+
|             |
+-------------+

Example

If we are passing more than one argument to tha CONCAT() function and anyone of the argument is null, then the result will also be null −

SELECT CONCAT('ALL', 'IS', 'WELL', NULL) AS CONCAT_Function;

Output

On executing the above query, it will produce the following output −

+-----------------+
| CONCAT_Function |
+-----------------+
| NULL            |
+-----------------+

Example

You can pass the table column as an argument to the CONCAT_WS() function to convert the character or string along with a seperator. Assume we have created a table with the name Customers using the CREATE statement as follows −

create table CUSTOMERS(
   ID INT NOT NULL, 
   NAME VARCHAR(15) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR(25), 
   SALARY DECIMAL(10, 4), PRIMARY KEY(ID)
);

Now let's insert seven records into the customers table using the INSERT statement as follows:−

insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(6, 'Komal', 22, 'MP', 4500.00);
insert INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(7, 'Muffy', 24, 'Indore', 10000.00);

The following SELECT query uses the CONCAT_WS function with the ID, NAME column with the seperator '_' of the CUSTOMERS table −

SELECT ID, NAME,  CONCAT_WS('__', ID, NAME) AS CONCAT_WSFunction from CUSTOMERS;

+----+----------+-------------------+
| ID | NAME     | CONCAT_WSFunction |
+----+----------+-------------------+
|  1 | Ramesh   | 1__Ramesh         |
|  2 | Khilan   | 2__Khilan         |
|  3 | kaushik  | 3__kaushik        |
|  4 | Chaitali | 4__Chaitali       |
|  5 | Hardik   | 5__Hardik         |
|  6 | Komal    | 6__Komal          |
|  7 | Muffy    | 7__Muffy          |
+----+----------+-------------------+

The following SELECT query uses the CONCAT_WS function with all the column of the CUSTOMERS table with the seperator ',' , the columns contains ID, NAME, AGE, ADDRESS and SALARY.

SELECT ID, NAME, AGE, ADDRESS, SALARY, CONCAT_WS(',', ID, NAME, AGE, ADDRESS, SALARY) AS CONCAT_WSFunction FROM CUSTOMERS;

Output

After executing the above statement, it produces the following output −

+----+----------+-----+-----------+------------+---------------------------------+
| ID | NAME     | AGE | ADDRESS   | SALARY     | CONCAT_WSFunction               |
+----+----------+-----+-----------+------------+---------------------------------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.0000 | 1,Ramesh,32,Ahmedabad,2000.0000 |
|  2 | Khilan   |  25 | Delhi     |  1500.0000 | 2,Khilan,25,Delhi,1500.0000     |
|  3 | kaushik  |  23 | Kota      |  2000.0000 | 3,kaushik,23,Kota,2000.0000     |
|  4 | Chaitali |  25 | Mumbai    |  6500.0000 | 4,Chaitali,25,Mumbai,6500.0000  |
|  5 | Hardik   |  27 | Bhopal    |  8500.0000 | 5,Hardik,27,Bhopal,8500.0000    |
|  6 | Komal    |  22 | MP        |  4500.0000 | 6,Komal,22,MP,4500.0000         |
|  7 | Muffy    |  24 | Indore    | 10000.0000 | 7,Muffy,24,Indore,10000.0000    |
+----+----------+-----+-----------+------------+---------------------------------+
sql-string-functions.htm
Advertisements