How to use CONCAT_WS in Sql Server



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 Syntax

The basic syntax of a CONCAT_WS is as follows:

SELECT CONCAT_WS (separator, string1, string2 [, stringN ]);

Getting Setup

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:

Now create a file called docker-compose.yml and add the following.

version: "3.9"
    image: ""
      - 1433:1433
        SA_PASSWORD: "Your_password123"
        ACCEPT_EULA: "Y"

Open a terminal and go to the folder the file is located. Then run the following.

docker-compose up

If you are looking for another good reference, you can check here:

Basic Example

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;
Jane Doe

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', '') as full_name;
Jane, Doe,

Using on columns

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) 
	('keith', 'holliday'),
	('jon', 'doe'),
	('jon', 'dane'),
	('jane', 'done'),
	('anna', 'smith');

We can now use CONCAT_WS to combine the first and last names.

	CONCAT_WS(' ', first_name, last_name) as full_name
FROM employees e;
keith holliday
jon doe
jon dane
jane done
anna smith