Articles Cookbook
Back to list
Standard SQL

How to Concatenate Strings in SQL

Database:

Operators:

CONCAT

Problem:

You want to join 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.

idfirst_namelast_name
1LoraSmith
2EmilBrown
3AlexJackson
4MartinDavis

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

Solution:

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

This query returns records in one column named full_name:

full_name
Lora Smith
Emil Brown
Alex Jackson
Martin Davis

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.

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.

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;
full_name
Lora Smith
NULL
Alex Jackson
Martin Davis

Look at the CONCAT function for the same data:

SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name
FROM student;
full_name
Lora Smith
Emil
Alex Jackson
Martin Davis

Recommended courses:

Recommended articles:

See also:

go to top