SQL Server CONCAT() Function

Summary: in this tutorial, you will learn how to use the SQL Server CONCAT() function to join multiple strings into one string.

Overview of SQL Server CONCAT() function

To join two or more strings into one, you use the CONCAT() function with the following syntax:

CONCAT ( input_string1, input_string2 [, input_stringN ] );Code language: SQL (Structured Query Language) (sql)

The CONCAT() takes two up to 255 input strings and joins them into one. It requires at least two input strings. If you pass one input string, the CONCAT() function will raise an error.

If you pass non-character string values, the CONCAT() function will implicitly convert those values into strings before concatenating.

The CONCAT() function also converts NULL into an empty string with the type VARCHAR(1).

Note that to add a separator during the concatenation, you use the CONCAT_WS() function.

SQL Server CONCAT() function examples

Let’s take some examples to get familiar with the CONCAT() function.

Using CONCAT() function with literal strings

The following example uses the CONCAT() function to concatenate three literal string John, space, and Doe:

SELECT 
    'John' + ' ' + 'Doe' AS full_name;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

full_name
---------
John Doe

(1 row affected)

Using CONCAT() function with table columns

This example uses the sales.customers table from the sample database.

The following statement uses the CONCAT() function to concatenate values in the first_name and last_name columns of the sales.customers table:

SELECT 
    customer_id,
    first_name,
    last_name,
    CONCAT(first_name, ' ', last_name) full_name
FROM 
    sales.customers
ORDER BY 
    full_name;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

Using CONCAT() function with NULL

The following example uses the CONCAT() function to format addresses for customers:

SELECT 
    CONCAT(
        CHAR(13),
        CONCAT(first_name,' ',last_name),
        CHAR(13),
        phone,
        CHAR(13),
        CONCAT(city,' ',state),
        CHAR(13),
        zip_code
    ) customer_address
FROM
    sales.customers
ORDER BY 
    first_name,
    last_name;
Code language: SQL (Structured Query Language) (sql)

Here is the partial output in the text format:

As clearly shown in the output, if the customer has no phone number, the CONCAT() function used an empty for the concatenation.

Note that we used the CHAR() function to get the new line character in this example.

Summary

• Use the SQL Server CONCAT() function to join two or more strings into one.