SQL Server String CONCAT_WS() Function

SQL Server CONCAT_WS() Function

The SQL Server CONCAT_WS() function is used to concatenate two or more strings with a specified separator. This function is useful for combining multiple columns or string literals into a single string with a defined separator for display or further processing.

Syntax

SELECT CONCAT_WS(separator, string1, string2, ...);

The CONCAT_WS() function takes the following arguments:

  • separator: The string used as a separator between concatenated strings.
  • string1, string2, ...: The strings to be concatenated.

Example SQL Server CONCAT_WS() Function Queries

Let's look at some examples of SQL Server CONCAT_WS() function queries:

1. Basic CONCAT_WS() Example

SELECT CONCAT_WS(', ', 'Apple', 'Banana', 'Cherry') AS result;

This query concatenates the strings 'Apple', 'Banana', and 'Cherry' with a comma and space as the separator. The result will be:

result
-----------------
Apple, Banana, Cherry

2. CONCAT_WS() with Columns

SELECT employee_id, CONCAT_WS(' ', first_name, last_name) AS full_name
FROM employees;

This query concatenates the first_name and last_name columns with a space as the separator for each employee. The result will show the employee_id and full_name.

3. CONCAT_WS() with Literals and Columns

SELECT CONCAT_WS(' - ', 'Employee', first_name, last_name) AS employee_description
FROM employees;

This query concatenates the literal 'Employee' with the first_name and last_name columns for each employee, separated by ' - '.

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the CONCAT_WS() function.

Step 1: Creating a Table

This step involves creating a new table named example_table to store some sample data.

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

In this example, we create a table named example_table with columns for id, first_name, and last_name.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the example_table.

INSERT INTO example_table (id, first_name, last_name) VALUES (1, 'John', 'Doe');
INSERT INTO example_table (id, first_name, last_name) VALUES (2, 'Jane', 'Smith');
INSERT INTO example_table (id, first_name, last_name) VALUES (3, 'Alice', 'Johnson');

Here, we insert data into the example_table.

Step 3: Using the CONCAT_WS() Function

This step involves using the CONCAT_WS() function to concatenate the first_name and last_name columns with a space as the separator.

SELECT id, CONCAT_WS(' ', first_name, last_name) AS full_name
FROM example_table;

This query retrieves the id, first_name, and last_name, and concatenates the first_name and last_name columns with a space in between for each row in the example_table. The result will be:

id  full_name
--- ----------------
1   John Doe
2   Jane Smith
3   Alice Johnson

Conclusion

The SQL Server CONCAT_WS() function is a powerful tool for concatenating strings with a specified separator. Understanding how to use the CONCAT_WS() function and its syntax is essential for effective string manipulation and data presentation in SQL Server.