Understanding CONCAT in SQL: A Basic Guide for Beginners

Want to concatenate two or more string columns in SQL? Learn how to use the SQL CONCAT function to concatenate strings.

When you query a database table, you may sometimes need to concatenate multiple text/string columns instead of retrieving data from a single column. This is helpful when you need an easier-to-interpret and readable output. 

For example, you can select a full_name field by concatenating the first_name and last_name fields. Similarly, you can get the full_address by concatenating the street, city, state, and other necessary fields.

In SQL, you can use the CONCAT function to concatenate strings. In this guide, we’ll go over:

• The syntax of the SQL CONCAT function
• Usage examples 
• Handling NULL values in one or more columns during concatenation

Let’s get started!

Syntax of the SQL CONCAT Function

The syntax to use the SQL CONCAT function is as follows:

CONCAT(string_1, string_2, ..., string_n);

Here, string_1, string_2, …, string n denote the strings to be concatenated. These can be string literals or columns or a combination of both.

Concatenating String Literals with CONCAT

Because the CONCAT function can also be used to concatenate string literals, let’s try to code a simple example.

Here we concatenate the strings ‘Hello, ‘ and ‘world!’ as the greeting string:

SELECT CONCAT('Hello, ', 'world!') AS greeting;

Running the above query will give you the following output:

 --------------- 
| greeting      |
 --------------- 
| Hello, world! |
 --------------- 
1 row in set (0.00 sec)

In practice, however, you may want to concatenate the required columns in a database table and not string literals. So let’s code some examples using the CONCAT function in SQL.

How to Concatenate Columns in SQL

Next, let’s move to querying a database table.

📑 All the sample queries in this tutorial were run on a MySQL database table. But you can also follow along in another RDBMS of your choice.

Creating a Database Table with Records

Let’s create a database that we can use:

CREATE DATABASE db1;

use db1;

Let’s create an employees table in the database db1. To do so, run the CREATE TABLE statement with the following columns and corresponding data types:

CREATE TABLE employees (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    street VARCHAR(100),
    city VARCHAR(50),
    state VARCHAR(2),
    username VARCHAR(20)
);

Next, let’s insert a few records into the employees table:

INSERT INTO employees (first_name, last_name, street, city, state, username) VALUES
    ('John', 'Smith', '123 Main St', 'New York', 'NY', 'john123'),
    ('Alice', 'Johnson', '456 Elm St', 'Boston', 'MA', 'alice456'),
    ('Bob', 'Williams', '789 Oak St', 'Chicago', 'IL', 'bob789'),
    ('Mary', 'Davis', '321 Pine St', 'Houston', 'TX', 'mary456'),
    ('James', 'Brown', '555 Cedar St', 'Seattle', 'WA', 'james789'),
    ('Emily', 'Jones', '777 Maple St', 'Atlanta', 'GA', 'emily123'),
    ('Michael', 'Miller', '999 Birch St', 'Miami', 'FL', 'michael456'),
    ('Jessica', 'Wilson', '111 Walnut St', 'Dallas', 'TX', 'jessica789'),
    ('William', 'Taylor', '222 Cherry St', 'Denver', 'CO', 'william123'),
    ('Sarah', 'Martinez', '444 Pine St', 'Phoenix', 'AZ', 'sarah456');

Example 1: Display Full Names

As a first example, let’s concatenate the first_name and the last_name columns to get the full_name. To do so, we can use the SQL CONCAT function in the SELECT query as shown:

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

You should get the following output:

 ---------------- 
| full_name      |
 ---------------- 
| John Smith     |
| Alice Johnson  |
| Bob Williams   |
| Mary Davis     |
| James Brown    |
| Emily Jones    |
| Michael Miller |
| Jessica Wilson |
| William Taylor |
| Sarah Martinez |
 ---------------- 
10 rows in set (0.00 sec)

In addition to the first_name and the last_name, notice that we have also used space as the separator—specified by the literal string ‘ ‘.

Example 2: Construct Addresses

Now let’s take another example.

We have the street, city, and state columns in the employees table. So we can select the full_address by concatenating these three fields with a comma as the separator:

SELECT CONCAT(street, ', ', city, ', ', state) AS full_address FROM employees;

Here’s the output:

 --------------------------- 
| full_address              |
 --------------------------- 
| 123 Main St, New York, NY |
| 456 Elm St, Boston, MA    |
| 789 Oak St, Chicago, IL   |
| 321 Pine St, Houston, TX  |
| 555 Cedar St, Seattle, WA |
| 777 Maple St, Atlanta, GA |
| 999 Birch St, Miami, FL   |
| 111 Walnut St, Dallas, TX |
| 222 Cherry St, Denver, CO |
| 444 Pine St, Phoenix, AZ  |
 --------------------------- 
10 rows in set (0.00 sec)

Example 3: Create Profile URLs

Recall that we have a username field in the employees table.

Say you have a root domain https://www.example.com/ and user profiles are at https://www.example.com/user. You can generate the profile_url using the CONCAT function like so:

SELECT CONCAT('https://www.example.com/user/', username) AS profile_url 
FROM employees;

As seen, we get the profile URLs for all the employees:

 ----------------------------------------- 
| profile_url                             |
 ----------------------------------------- 
| https://www.example.com/user/john123    |
| https://www.example.com/user/alice456   |
| https://www.example.com/user/bob789     |
| https://www.example.com/user/mary456    |
| https://www.example.com/user/james789   |
| https://www.example.com/user/emily123   |
| https://www.example.com/user/michael456 |
| https://www.example.com/user/jessica789 |
| https://www.example.com/user/william123 |
| https://www.example.com/user/sarah456   |
 ----------------------------------------- 
10 rows in set (0.00 sec)

Handling NULL Values

In the employees table, all the records have all the fields. But what if you have one or more fields with NULL values?

Let’s take an example for this case. Here we update the record corresponding to ID = 2 to set the street column to NULL:

UPDATE employees
SET street = NULL
WHERE ID = 2; -- Update the record with ID 2

Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Now we use CONCAT to select the full_address:

SELECT CONCAT(street, ', ', city, ', ', state) AS full_address FROM employees;

Here’s the output:

 --------------------------- 
| full_address              |
 --------------------------- 
| 123 Main St, New York, NY |
| NULL                      |
| 789 Oak St, Chicago, IL   |
| 321 Pine St, Houston, TX  |
| 555 Cedar St, Seattle, WA |
| 777 Maple St, Atlanta, GA |
| 999 Birch St, Miami, FL   |
| 111 Walnut St, Dallas, TX |
| 222 Cherry St, Denver, CO |
| 444 Pine St, Phoenix, AZ  |
 --------------------------- 
10 rows in set (0.00 sec)

Notice that the second element in the result set is NULL.

But we’d have liked the output to be the concatenation of the city and the state columns to get a rough idea of the address. When you have such NULL values, you can use CONCAT_WS as an alternative to the CONCAT function. Let’s see how it works.

Using CONCAT_WS to Handle NULL Values During Concatenation

CONCAT_WS is an alternative to CONCAT that can be used if you suspect one or more fields to contain NULL values.

You can use the CONCAT_WS function like so:

CONCAT_WS(separator, string_1, string_2,..., string_n)

Now run the following SELECT query:

SELECT CONCAT_WS(', ', street, city, state) AS full_address FROM employees;

You’ll get the following output:

 --------------------------- 
| full_address              |
 --------------------------- 
| 123 Main St, New York, NY |
| Boston, MA                |
| 789 Oak St, Chicago, IL   |
| 321 Pine St, Houston, TX  |
| 555 Cedar St, Seattle, WA |
| 777 Maple St, Atlanta, GA |
| 999 Birch St, Miami, FL   |
| 111 Walnut St, Dallas, TX |
| 222 Cherry St, Denver, CO |
| 444 Pine St, Phoenix, AZ  |
 --------------------------- 
10 rows in set (0.01 sec)

As seen, for the second item in the result set, we get ‘Boston MA’ as the street field is NULL.

⚠ When using CONCAT_WS, it’s required to specify the separator. If you do not specify the separator, then the result is NULL if one or more columns is NULL (similar to CONCAT).

Wrapping Up

Let’s review what we’ve learned:

• When you are querying a database table to retrieve data, you may want to concatenate multiple string columns to get more helpful and easy-to-interpret query results. To do so, you can use the CONCAT function in SQL with the syntax CONCAT(string_1, string_2, ..., string_n).
• You can concatenate string literals, columns, or a combination of both. However, if there are one or more NULL values, the result for that particular record is NULL. To handle this, you can use CONCAT_WS with the syntax CONCAT_WS(separator, string_1, string_2, ..., string_n).
• CONCAT_WS handles NULL values more gracefully by concatenating only those strings which are present using the specified separator.

For quick review of SQL commands and their usage, you can bookmark this SQL Cheat Sheet.