How to use CONCAT_WS in Sql Server

Intro

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: 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.

docker-compose up

If you are looking for another good reference, you can check here: https://docs.docker.com/samples/aspnet-mssql-compose/.

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;

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;

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) 
	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;