SQL CONCAT_WS Function Example | CONCAT_WS Method In SQL

SQL CONCAT_WS Function Example | CONCAT_WS Method In SQL is today’s topic. The SQL CONCAT_WS function is used to join two or more expressions separated by a separator. It takes up to 255 input strings which are further joined by a function. If we want to perform the join operation, CONCAT requires at least 2 strings. If it is provided with only 1 string, it will raise the error.

SQL CONCAT_WS Function

If any non-character string values are passed into the function, it will be implicitly converted to the strings before concatenating.

If NULL is added to the function, it converts it into the empty string with VARCHAR (1).

See the following syntax.

SELECT CONCAT_WS (separator, expression1, expression2, expression3…)

Parameters

Separator

This is the separator that is added between more than one expression. If the separator is NULL, then the function will return NULL.

expression1, expression2, expression3…:

These are the expressions that will be concatenated.

See the following example. We are using SQL SELECT Statement.

SELECT CONCAT_WS ('.', 'AppDividend', 'com');

Output

AppDividend.com

Explanation

Here “.” acts as a separator and is added between AppDividend and com.

See the following code.

SELECT CONCAT_WS (',', 1, 2, NULL, NULL, 3);

Output

1, 2, 3

Explanation

The above example demonstrated how concat_ws() function deals with NULL values. Here, the NULL values were ignored, and no separator was added between them.

Above were all the common examples to make clear how the Concat function works.

Let’s see the example with proper tables.

Consider table Employee.

Emp_id	First_name	Last_name	City	State	Salary
101	Rohit	Raj	Patna	Bihar	30000
201	Shivam	Rana	Jalandhar	Punjab	20000
301	Karan	Kumar	Allahabad	Uttar Pradesh	40000
401	Suraj	Bhakat	Kolkata	West Bengal	60000
501	Akash	Cherukuri	Vizag	Andhra Pradesh	70000

Now, suppose we want the full name of an employee from this table. Then, the following query has to be executed.

Query

Select First_name, Last_name, concat_ws(‘ ’, First_name, Last_Name) 
AS Full_name from Employee;

Output

First_name	Last_name	Full_name
Rohit	Raj	Rohit Raj
Shivam	Rana	Shivam Rana
Karan	Kumar	Karan Kumar
Suraj	Bhakat	Suraj Bhakat
Akash	Cherukuri	Akash Cherukuri

Here you can see that the full name of the employee is displayed separated by a separator which was space.

The CONCAT_WS() function joins the input strings into the single string.

It separates those concatenated strings with a separator particular in the first argument.

One thing to note that the CONCAT_WS() requires at least two input strings.

It means that if we pass zero or one input string argument, the function will raise the error.

The CONCAT_WS() function treats NULL as the empty string of type VARCHAR().

It also does not add a separator between NULLs.

Therefore, a CONCAT_WS() function can cleanly join the strings that may have blank values.

Use CONCAT_WS() function with table columns

The following statement uses the CONCAT_WS() function to join the values in the last_name and first_name columns of the sales.customer table.

It separates the last name and first name by the comma (,) and space.

SELECT 
    first_name, 
    last_name, 
    CONCAT_WS(', ', last_name, first_name) full_name
FROM 
    sales.customers
ORDER BY 
    first_name, 
    last_name;

Use CONCAT_WS() Function with NULL

The following statement indicates how the CONCAT_WS() function handles input strings that have NULL values.

SELECT 
    CONCAT_WS(',', 1, 2, NULL, NULL, 3);

The output is as follows:

result
----------------------------------------
1,2,3
 
(1 row affected)

As you can see from the above output, the CONCAT_WS() function ignores NULL and don’t add the separator between the NULL values.

The following example concatenates the customer data to format customer’s addresses. If the customer does not have a phone number, that function ignores it.

SELECT 
    CONCAT_WS
    (
        CHAR(13), 
        CONCAT(first_name, ' ', last_name), 
        phone, 
        CONCAT(city, ' ', state), 
        zip_code,
        '---'
    ) customer_address
FROM 
    sales.customers
ORDER BY 
    first_name, 
    last_name;

Using CONCAT_WS() to generate CSV file

This statement uses the comma (,) as a separator and concatenates values in the first_name, last_name, and email column to generate the CSV file.

See the following query.

SELECT 
    CONCAT_WS(',', first_name, last_name, email)
FROM 
    sales.customers
ORDER BY 
    first_name, 
    last_name;

So, In this tutorial, you have learned how to use the SQL CONCAT_WS() function to concatenate strings with a separator.

Finally, SQL CONCAT_WS Function Example is over.

SQL CONCAT_WS Function

Parameters

Separator

expression1, expression2, expression3…:

Output

Explanation

Output

Explanation

Query

Output

Use CONCAT_WS() function with table columns

Use CONCAT_WS() Function with NULL

Using CONCAT_WS() to generate CSV file

Recommended Posts