For an instructor lead, in-depth look at learning SQL click below.
The CONCAT_WS function in SQL is designed to consolidate several expressions or strings into a single value, with each one separated by a specified separator. CONCAT_WS is an abbreviation for ‘Concatenate With Separator’. This function is highly useful in SQL and is often employed in domains like data manipulation and data cleaning.
Definition
The CONCAT_WS function works by concatenating the inputs after transforming them into strings. However, it also includes a separator between each concatenated item. The general syntax of this function is:
|
1 2 3 |
CONCAT_WS ( separator, expression [, expression]... ) |
It’s important to highlight that the separator can be any SQL expression. If the separator is NULL, the function will treat it as an empty string.
Practical Example of CONCAT_WS
As an example, let’s consider a database that holds a table named “Employees” with the following data:
| First Name | Last Name | |
|---|---|---|
| John | Doe | jdoe@example.com |
| Jane | Smith | jsmith@example.com |
If we want to merge the first name, last name and email of each employee into a single string with comma as a separator, we can use the CONCAT_WS function. The SQL code would look like this:
|
1 2 3 4 |
SELECT CONCAT_WS(',', First_Name, Last_Name, Email) AS 'Employee_Details' FROM Employees; |
The output of this request will be:
|
1 2 3 4 |
'John, Doe, <a href="mailto:jdoe@example.com'" >jdoe@example.com'</a>, 'Jane, Smith, <a href="mailto:jsmith@example.com'" >jsmith@example.com'</a> |
This is a simple yet very practical example of how to use the CONCAT_WS function in SQL to combine a series of variable length strings into a standard format. The CONCAT_WS function is thus a powerful tool in a SQL programmer’s toolbox!