How to Concatenate Two Columns in SQL – A Detailed Guide

In SQL, concatenation is the operation of joining together strings, or pieces of text information. Learn how to concatenate two columns in SQL with this detailed guide.

SQL concatenation is the process of combining two or more character strings, columns, or expressions into a single string. For example, the concatenation of 'Kate', ' ', and 'Smith' gives us 'Kate Smith'.

Enhance your SQL skills with our interactive SQL Practice Set! This comprehensive course contains nearly 90 hands-on exercises, divided into five topic-based sections, including a section dedicated to SQL concatenation. It's an excellent way to refresh your SQL knowledge and build confidence in your skills with each exercise solved. Don't miss this opportunity to practice SQL, including SQL concatenation, in a structured and interactive way.

SQL concatenation can be used in a variety of situations where it is necessary to combine multiple strings into a single string. Some common use cases include:

• Creating full names or other composite strings from multiple columns in a table – e.g. concatenating a user’s first and last names to create a full name.
• Creating custom labels or titles by concatenating multiple string values.
• Creating a unique identifier by concatenating multiple columns – e.g. a customer ID and an order number.
• Creating email addresses by concatenating a username (katesmith) and a domain name (learnSQL.com).

These are just a few examples, but SQL concatenation can be used in many other situations where combining multiple strings is necessary.

How to Concatenate Two Columns in SQL

The syntax for SQL concatenation can vary depending on the specific SQL dialect being used. Here are a few examples of the most well-known techniques for SQL concatenation.

The || Operator

Standard SQL uses the || operator (as well as a few other options). Most SQL databases, with the notable exception of SQL Server, support this operator. The || operator takes two or more arguments and returns a single concatenated string.

Usage

Let’s imagine the following case. We have a table called users that stores user information:

Let’s use the || operator to concatenate users’ first and last names and get a full name:

SELECT first_name || ' ' || last_name AS full_name 
FROM users;

The result:

In this example, the || operator takes the first_name and last_name columns from the users table and concatenates them together with a space in between, resulting in a full name for each user. The result is then aliased as full_name and returned in the query results.

You can concatenate multiple strings using the || operator by providing more than two arguments. For example, the following SQL statement concatenates users’ first, middle, and last names:

SELECT first_name || ' ' || middle_name || ' ' || last_name AS full_name 
FROM users;

The result:

The Concatenation Operator || Works with Non-Text Columns Too

The two previous examples only contain strings; what about the other data types? The arguments of the || operator can be strings, text columns, expressions, and other data types like numbers or dates. They will be automatically converted to a string.

Here is an example of how to use the || operator to concatenate the first name, the last name, and the age of a user to form a new string:

SELECT 
  first_name || ' ' || last_name || ': ' || age || ' yo' AS user_details 
FROM users;

The result:

Note that the first argument of the concatenation must be of a text data type. If not, you need to cast it into a text type. Here is an example of how to use the || operator to concatenate the ID and the first name of a user to form a unique identifier:

SELECT 
  cast(id as VARCHAR) || '_' || first_name AS unique_id 
FROM users;

The result:

Beware of NULLs!

The || concatenation operator returns NULL for any NULL argument.

Imagine that you have some empty or NULL values in your database:

If one of the arguments of the concatenation is NULL, the whole expression returns NULL. In the following example, we’ll try a concatenation with a NULL argument:

SELECT 
  first_name || ' ' || last_name AS full_name 
FROM users;

The result:

The second result here is NULL: Robocop has a first name, but his middle and last names are NULL. If you concatenate 'Robocop' with NULL, you get NULL.

Note: In the case of Oracle, a NULL string is an empty string. The concatenation “ignores” the NULL string, and the concatenated arguments are returned. Oracle will return the following:

Use the COALESCE() Function to Concatenate NULL Values

An incredible tip for avoiding NULL values is using the COALESCE() function. The COALESCE() function in SQL is a built-in function that returns the first non-NULL value in a list of expressions. The function takes one or more arguments and returns the first argument that is not NULL.

In the following example, suppose that the last_name field could be NULL. We want to avoid a bad concatenation, so we use the COALESCE() function:

SELECT 
  first_name || ' ' || COALESCE(last_name, '') AS full_name 
FROM users;

The result:

Above, COALESCE() returns the last name if the last name is not NULL. If the last name is NULL, it returns the empty string ‘’. This allows us to avoid concatenating the NULL value of Robocop’s last name and getting a NULL result.

The + Operator

The + operator is used to concatenate strings in MS SQL Server. It takes two or more arguments and returns a single concatenated string.

Here is an example of using the + operator to concatenate a user’s first and last names:

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

The result:

With the + operator, none of the arguments will be converted automatically. Each argument of the expression needs to be of the VARCHAR type if the concatenation will be successful. To concatenate values of different types, we use the CAST() function:

SELECT 
  first_name + ' ' + last_name + ': ' + CAST(age AS VARCHAR) + ' yo' AS user_details 
FROM users;

The result:

Without CAST(), SQL Server will throw the following error:

MS SQL
Token error: 'Error converting data type varchar to bigint.' 

The CONCAT Function

CONCAT() is another standard SQL built-in function that concatenates two or more values. All popular database systems except SQLite accept this function; SQLite only accepts the || operator.

The main differences between the || and + operators and the CONCAT() function are:

• CONCAT() arguments are automatically converted into strings, so you can use arguments of different data types.
• CONCAT() treats NULL values as empty strings and ignores them. The exception here is MySQL (and, to a lesser extent, MariaDB). In MySQL, the CONCAT() function is equivalent to the || operator; NULL arguments yield NULL results.

Here is an example of using CONCAT() to concatenate users’ first and last names:

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

The result:

In the following example, we’ll use CONCAT() to concatenate users’ ID and first name to form a unique identifier. Note that the CAST() function is not required:

SELECT 
  CONCAT(id, '_', first_name) AS unique_id 
FROM users;

The result:

The CONCAT_WS Function

The CONCAT_WS function in SQL is similar to the CONCAT function, but it is used to concatenate two or more strings together with a separator. The function takes two arguments: the first argument is the separator, and the rest of the arguments are the strings you want to concatenate.

All popular databases except Oracle and SQLite accept the CONCAT_WS function. This function ignores NULLs in arguments (other than the separator) – even for MySQL.

Let’s use the CONCAT_WS function to concatenate first_name and last_name values with a space separator:

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

The result:

Finally, let’s try concatenating three arguments, separated by spaces:

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

The result:

Concatenating Two Columns In SQL Is Easy!

I hope you enjoyed reading this article as much as I did writing it! The differences between SQL dialects are subtle, and it’s always exciting knowing more about them.

Did this article encourage you to learn more about SQL? Don’t hesitate! Your SQL journey is already off to a good start, and LearnSQL.com has all the resources and help you need to become an SQL expert.

Mastering SQL is a hands-on experience — learning the concepts is one thing, but writing good queries requires practice. I sincerely recommend our SQL Practice track with over 1000 exercises in 10 different interactive courses. Each course includes practical exercises based on realistic databases and real-world scenarios.

So, what are you waiting for? Don’t just stick with concatenating columns – learn more SQL today!