intro
Let's explore what the CONCAT_WS function has to offer when it comes to string concatenation in SQL Server!
String concatenation is a common operation when dealing with text data in a database. Here is why SQL Server provides some functions and operators to make that easier. This is where the CONCAT_WS SQL Server function comes in!
In this article, you will learn what CONCAT_WS is, how this string concatenation function works, and how to use it in real-world Transact-SQL examples!
Time to dive in!
What Is the CONCAT_WS SQL Server Function?
CONCAT_WS is a SQL Server function that accepts a separator and a list of strings. Given those arguments, it returns a string resulting from the concatenation of the string in the list using the specified separator. In other words, the CONCAT_WS SQL Server function takes a variable number of strings and concatenates them into a single string according to a separator.
“CONCAT_WS” stands for “concatenate with separator” and is one of the most common database functions to deal with string concatenation. Other popular approaches involve the CONCAT function and the + operator applied on strings.
SQL Server CONCAT_WS(): Syntax and How to Use It
In Transact-SQL, the SQL CONCAT_WS function has the following syntax:
1
CONCAT_WS(separator, argument1, argument2 [, ..., argumentN])
The arguments accepted by the function are:
Keep in mind that CONCAT_WS requires a separator and a minimum of two other string arguments. Otherwise, SQL Server will raise the following error (the contents of the error will differ in other database management systems):
1
[Code: 189, SQL State: S1000] The concat_ws function requires 3 to 254 arguments.
To better understand how the function works, consider the CONCAT_WS SQL example query below:
1
SELECT CONCAT_WS('-', 'Aa', 'Bb', 'Cc', 'Dd');
This returns the string 'Aa-Bb-Cc-Dd'.
The CONCAT_WS SQL Server function implicitly converts all arguments to strings before concatenation. The implicit conversion to strings follows the Transact-SQL rules for data type conversions. Thus, you can also use it with non-explicit string formats as below:
1
SELECT CONCAT_WS('-', 1, 'A', 2, 'B');
The resulting string is 1-A-2-B as if 1 and 2 were strings.
Note that CONCAT_WS ignores NULL values during concatenation, avoiding adding separators between them:
If the function receives arguments with all NULL values, it returns an empty string.
Use Cases of the SQL CONCAT_WS Function in Transact-SQL
Let's learn how to use CONCAT_WS in SQL Server by showing some of its most common use cases through real-world examples.
The following CONCAT_WS SQL Server queries will be executed in DbVisualizer, the database client with the highest user satisfaction in the market!
This is the table the statements will be run on:
As you can see, it is called Employee and stores information about the employees in a company.
Data Aggregation
The basic use case of the SQL CONCAT_WS function is to concatenate two or more columns to produce useful aggregate data. For example, you can use it to get complete names out of individual names and surnames:
1
SELECT Id, CONCAT_WS(' ', Name, Surname) as CompleteName
2
FROM Employee;
Great, the name strings have been aggregated as desired!
Address Concatenation
Since CONCAT_WS does not add the separator between NULL values, it is a great tool for concatenating strings that might have “blank” values. A common example is the concatenation of addresses.
Assume your target table has a column for the first address and a column for the second address. You can concatenate them consistently with the following query:
1
SELECT Id, Name, Surname, CONCAT_WS(' - ', FirstAddress, SecondAddress) as CompleteAddress
2
FROM Employee;
Awesome! When SecondAddress is NULL, the SQL Server CONCAT_WS function returns only FirstAddress as expected.
Generate Data in CSV Format
Suppose your goal is to get CSV-formatted data from a table. The first approach you might come up with is the following query:
1
SELECT STRING_AGG(CONCAT_WS(',', Id, Name, Surname, FirstAddress, SecondAddress, Role), CHAR(13)) AS CsvData
2
FROM Employee;
In this example, STRING_AGG concatenated the values of string expressions by placing a CHAR(13) (carriage return) between them. Simply put, it populated each line of the resulting CSV-formatted text.
Perfect, this time the query works like a charm!
Congratulations, you are now a SQL Server CONCAT_WS expert!
Conclusion
In this guide, you understood what the CONCAT_WS function is and how it works. This powerful string concatenation function helps you aggregate strings in multiple database management systems including SQL Server, MySQL, and PostgreSQL even when they have NULL values. Thanks to the real-world use cases shown above, you also learned how to use it.
As proven by the CSV generation example, string concatenation can be tricky, so to better understand how to use the SQL CONCAT_WS function, you need a powerful database client like DbVisualizer! In addition to presenting data visually, it supports several DBMS technologies, offers advanced query optimization capabilities, and can generate ERD-like schemas. Try DbVisualizer for free today!
FAQ
How does the CONCAT_WS SQL Server function differ from CONCAT?
The CONCAT_WS function in SQL Server is specifically designed for concatenating values with a specified separator. It allows you to concatenate multiple values, handling NULL values gracefully. In contrast, the CONCAT function simply concatenates values without any separator.
Is it possible to use NULL or an empty string as a separator with CONCAT_WS?
Yes, you can use NULL or an empty string as a separator with CONCAT_WS. In both cases, the function concatenates the string arguments with an empty string. So, for instance, CONCAT_WS('', 'A', 'B', 'C') would produce the string 'ABC'.
When is CONCAT_WS better than other string concatenation methods in SQL Server?
CONCAT_WS is particularly useful when concatenating values with a specific separator, as it allows for cleaner and more readable SQL code. It also excels in scenarios where you need to handle NULL values consistently.
What is the CONCAT_WS equivalent in MySQL?
In MySQL, the equivalent function to SQL Server’s CONCAT_WS is also called CONCAT_WS. The syntax and functionality are the same between the two databases, so they are direct equivalent functions.
How does the SQL Server CONCAT_WS function handle NULL values?
The SQL Server CONCAT_WS function simply skips NULLs as they were not arguments. In detail, it inserts the separator only between non-NULL values.
