The SQL Server CONCAT_WS will allow you to join multiple strings using a separator. This is similar to CONCAT, however you can use a separator. In this article, we will learn how to use the CONCAT function in SQL Server.
The basic syntax of a CONCAT_WS is as follows:
SELECT CONCAT_WS (separator, string1, string2 [, stringN ]);
For this, we will be using docker. This is recommended for more than just using SQL Server. To find how to install docker go here: https://docs.docker.com/engine/install/
Now create a file called
docker-compose.yml and add the following.
version: "3.9" services: db: image: "mcr.microsoft.com/mssql/server" ports: - 1433:1433 environment: SA_PASSWORD: "Your_password123" ACCEPT_EULA: "Y"
Open a terminal and go to the folder the file is located. Then run the following.
If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.
The simple example is to use two or more string to combine to one. Often you will have a first and last name, so you will want to combine into a full name. Let's try that.
SELECT CONCAT_WS(' ', 'Jane', 'Doe') as full_name;
Notice we supply the separate first. We can continue to add strings at the end to join and only need to specifiy the separate once.
SELECT CONCAT_WS(', ', 'Jane', 'Doe', 'Jane.Doe@gmail.com') as full_name;
|Jane, Doe, Jane.Doe@gmail.com|
Now let's try this on a table. Let's create an employee table and insert some rows.
CREATE TABLE employees ( first_name VARCHAR (50) NOT NULL, last_name VARCHAR (50) NOT NULL, ); insert into employees (first_name, last_name) values ('keith', 'holliday'), ('jon', 'doe'), ('jon', 'dane'), ('jane', 'done'), ('anna', 'smith');
We can now use CONCAT_WS to combine the first and last names.
SELECT CONCAT_WS(' ', first_name, last_name) as full_name FROM employees e;