Using SQL CONCAT Function to Concatenate Two or More Strings

Summary: in this tutorial, you will learn how to use the SQL CONCAT function to concatenate two or more strings into a single string.

Introduction to SQL CONCAT function

The SQL CONCAT function concatenates two or more strings into one string. The following illustrates the syntax of the CONCAT function:

CONCAT(string1,string2,..);Code language: SQL (Structured Query Language) (sql)

To concatenate strings, you pass the strings as a list comma-separated arguments to the function.

The CONCAT function returns a string which is the combination of the input strings. It returns NULL if one of the argument is NULL. (To handle NULL values more effectively you can use the IS NULL operator or COALESCE and NULLIF functions.)

Most relational database systems support the CONCAT function with some differences among them. For example, MySQL CONCAT function allows you to concatenate more than two strings whereas Oracle CONCAT function concatenates exactly two strings.

Besides using the CONCAT function, you can use the concatenation operator e.g., in Oracle and PostgreSQL you can use the || operator to concatenate two or more strings. And in Microsoft SQL Server, you use the + operator.

SQL CONCAT examples

The following statement uses the CONCAT function to concatenate two strings:

SELECT CONCAT('SQL CONCAT function', ' demo');Code language: SQL (Structured Query Language) (sql)

        concat
----------------------
 SQL CONCAT function demo
(1 row)Code language: SQL (Structured Query Language) (sql)

The following statement uses the CONCAT function to return the full name of the employees by concatenating the first name, space, and last name.

SELECT 
    CONCAT(first_name, ' ', last_name) AS name
FROM
    employees
ORDER BY name;Code language: SQL (Structured Query Language) (sql)

If you are using Oracle database, you have to apply the CONCAT function twice the achieve the same result. See the following query:

SELECT 
    CONCAT(CONCAT(first_name, ' '), last_name) AS name
FROM
    employees
ORDER BY name;Code language: SQL (Structured Query Language) (sql)

The inner CONCAT function concatenates the first name with space, and the outer CONCAT function concatenates the result of the inner CONCAT function with the last name.

It will be much cleaner if you use the concatenation operator in Oracle (and also PostgreSQL).

SELECT 
    (first_name || ' ' || last_name) AS name
FROM
    employees
ORDER BY name;Code language: SQL (Structured Query Language) (sql)

In Microsoft SQL Server, you would use the following query:

SELECT 
    (first_name + ' ' + last_name) AS name
FROM
    employees
ORDER BY name;Code language: SQL (Structured Query Language) (sql)

If you are using MySQL or PostgreSQL, you can use the CONCAT_WS function to concatenate strings with a separator.

CONCAT_WS(separator,string1,string2,...);Code language: SQL (Structured Query Language) (sql)

For example, you can use the CONCAT_WS function to construct the full name of the employee as follows:

SELECT 
    CONCAT_WS(' ',first_name,last_name) AS name
FROM
    employees
ORDER BY name;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL CONCAT function to concatenate two or more strings into a single string.