What is CONCAT in SQL?

Introduction

The CONCAT function in Structured Query Language (SQL) connects or concatenates two or more strings into a single string. This feature is crucial for data formatting and modification, which makes it an indispensable tool for database developers and administrators. Furthermore, concatenating strings can be done with the + operator in certain SQL dialects. The syntax, use, and real-world examples of the CONCAT function—including concatenating strings with the + operator—will all be covered in this article.

Overview

• The CONCAT function in SQL combines multiple strings into one string, essential for data formatting and modification.
• CONCAT syntax involves passing two or more strings as arguments to return a concatenated result. It applies to various tasks, such as joining columns and formatting data.
• Examples demonstrate basic concatenation, using separators, and handling NULL values with the CONCAT function and the + operator in SQL Server.
• The CONCAT_WS function allows easy string concatenation with a specified separator, providing cleaner and more readable syntax.
• Mastering CONCAT and related functions like CONCAT_WS enhances SQL querying skills, aiding in efficient string manipulation and data presentation.

Syntax of CONCAT

CONCAT(string1, string2, ..., stringN)

In this syntax, string1, string2 …, and stringN are the strings that need to be concatenated, and this function can take two or more string arguments and will return a single concatenated string.

The CONCAT function can be applied to several tasks, including joining columns, displaying data in a formatted manner, and generating new string values from preexisting ones. Additionally, strings can be concatenated using the + operator in some SQL dialects, such as SQL Server. Now that we know more about its application, let’s look at real-world examples.

Now, let’s see some examples.

Example 1: Basic Concatenation

Suppose you have a table employee with this structure

CREATE TABLE employees (
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

Add the data to the table

INSERT INTO employees (first_name, last_name) VALUES ('Badri', 'BN');
INSERT INTO employees (first_name, last_name) VALUES ('Abhishek', 'Kumar');
INSERT INTO employees (first_name, last_name) VALUES ('Mounish', 'Kumar');
INSERT INTO employees (first_name, last_name) VALUES ('Santosh', 'Reddy');

The output will be:

Now  concatenate the first_name and last_name columns to get the full name of each employee using the CONCAT function:

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

The output will be:

Or, if you are using SQL Server, you can use the + operator for concatenation:

SELECT first_name + ' ' + last_name AS full_name
FROM employees;

The output will be:

Example 2: Using a Separator to Concatenate Columns

You can pass a separator as an input to the CONCAT function to add one between concatenated values. To generate email addresses, for example, using the first and last names:

SELECT CONCAT(first_name, '.', last_name, '@example.com') AS email
FROM employees;

The output will be:

In SQL Server, use the + operator:

SELECT first_name + '.' + last_name + '@example.com' AS email
FROM employees;

The output will be:

Example 3: Handling NULL Values 

The way the CONCAT function behaves with NULL values is one of its key features. The CONCAT function will proceed with concatenation if any argument is NULL, treating it as an empty string. You can use the COALESCE function to supply a default value if you want to handle NULL values explicitly:

But before this, let’s add a column that has a null value 

INSERT INTO employees (first_name) VALUES ('John');

The output will be:

Now let’s see how COALESCE works with null values

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

The output will be:

CONCAT_WS Function

The CONCAT_WS (Concatenate With Separator) function, another feature of SQL, makes concatenating strings with a separator easier. CONCAT_WS syntax is as follows:

CONCAT_WS(separator, string1, string2, ..., stringN)

For example, Let’s  concatenate the first name and last name with a space separator:

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

The output will be:

The result will be the same as using the CONCAT function with explicit separators, but the syntax is cleaner and easier to read.

Conclusion

You can combine numerous strings into one by using SQL’s CONCAT function, which is a potent tool for string manipulation. Knowing how to utilize CONCAT well will improve your SQL querying skills, whether you are managing NULL values, generating new string values, or formatting data for presentation. Additionally, the + operator in SQL Server gives another approach for string concatenation, and the CONCAT_WS function offers a convenient way to add separators in your concatenated strings. Gaining proficiency with these operators and functions will enable you to easily handle various data manipulation jobs.

Frequently Asked Questions

JanviKumari01 29 Jul, 2024

Hi I am Janvi Kumari currently a Data Science Intern at Analytics Vidhya, passionate about leveraging data for insights and innovation. Curious, driven, and eager to learn. If you'd like to connect, feel free to reach out to me on LinkedIn