SQL CONCAT_WS Example | CONCAT_WS Method In SQL
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…)
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');
Here “.” acts as a separator and is added between AppDividend and com.
See the following code.
SELECT CONCAT_WS (',', 1, 2, NULL, NULL, 3);
1, 2, 3
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.
Now, suppose we want the full name of an employee from this table. Then, the following query has to be executed.
Select First_name, Last_name, concat_ws(‘ ’, First_name, Last_Name) AS Full_name from Employee;
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.