How to Concatenate Strings in SQL

Problem:

You want to concatenate strings from two columns of a table into one.

Example:

Our database has a table named student with data in the following columns: id, first_name and last_name.

Let’s append the first name to the last name of the student in one string. Use a space between each name.

Solution 1: || Operator

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

This query returns records in one column named full_name:

Discussion:

To append a string to another and return one result, use the || operator. This adds two strings from the left and right together and returns one result. If you use the name of the column, don’t enclose it in quotes. However, in using a string value as a space or text, enclose it in quotes.

In our example, we added a space to first_name and then the column last_name. This new column is called full_name.

Solution 2: The CONCAT Function

You can also use a special function: CONCAT. It takes a list of strings or names of columns to join as arguments:

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

The results are identical.

Discussion 2:

However, the CONCAT() function is better for retrieving data from a column with NULL values. Why? Because, when a NULL is included in the values to be joined, the operator returns NULL as a result. In the case of CONCAT(), NULL will not be displayed.

Look at the result of the || operator if Emill doesn’t have a last name recorded:

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

Look at the CONCAT function for the same data:

SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name
FROM student;