AppDividend
Latest Code Tutorials

SQL CONCAT_WS Function Example | CONCAT_WS Method In SQL

0

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.

Recommended Posts

SQL DIFFERENCE Function Example

SQL LIKE Operator Tutorial With Example

Understand SQL Constraints

SQL Operators Tutorial With Example

SQL Except Clause Example

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.