How to use CONCAT in Sql Server

Intro

The SQL Server CONCAT function allows you to combine two or more strings into one single string. You can use this on strings or columns in a table. In this article, we will learn how to use the CONCAT function in SQL Server.

The Syntax

The basic syntax of a CONCAT is as follows:

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

Notice that we had to add a space ourselves. The CONCAT_WS function would allow you to add space between strings.

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 to combine the first and last names.

SELECT 
	CONCAT(first_name, ' ', last_name) as full_name
FROM employees e;