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
The post SQL CONCAT_WS Function Example | CONCAT_WS Method In SQL appeared first on AppDividend.
via Planet MySQL
SQL CONCAT_WS Function Example | CONCAT_WS Method In SQL
