CONCAT SQL | SQL Server CONCAT() Function With Example

Summary: in this tutorial, you will see What is CONTACT SQL and How to use the SQL Server CONCAT() function to join multiple strings into one string.

Definition and Usage

The CONCAT SQL Server function used to adds two or more strings together or To join two or more strings into one, there is the following syntax

CONTACT SQL Synatx

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

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

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

CONCAT SQL Function Examples

Let’s see the some example to get familiar with the SQL CONCAT() function.

A) Using SQL CONCAT() function with literal strings

The following given below example uses the CONCAT SQL function to concatenate three literal string David, space, and Shayala:

SELECT 
    'David' + ' ' + 'Shayala' AS full_name;

Therefore, let’s execute the query to return the output.
Output:

full_name
---------
David Shayala
(1 row affected)

B) Using CONCAT() function with table columns

Here, we will use the Sample Demo table "Customers".

The following statement uses the SQL CONCAT() function to concatenate values in the CustomerName, City, Country, and PostalCode columns of the customers table:

SELECT 
    CustomerName,
    City,
	Country,
	PostalCode,
    CONCAT(CustomerName, ' ', City, '', Country, '', PostalCode) full_address
FROM 
    customers
ORDER BY 
    CustomerName;

The following given below picture shows the output:
Output:

C) Using CONCAT() function with NULL

Here, we will use the Sample Demo table "Customers".

This following given below example uses the SQL CONCAT() function to format addresses for customers:

SELECT 
    CONCAT(
        CHAR(12),
        CustomerName,
        CHAR(12),
        City,
        CHAR(12),
        CONCAT(PostalCode,' ',Country)
    ) customer_address
FROM
    customers
ORDER BY 
    CustomerName

The following given below picture shows the output:
Output:

Therefore, as you can see in the output image if the customers have not to mention the country name, then the CONCAT() function used an empty for the concatenation.

Conclusion

In this tutorial, you have learned What is SQL CONCAT() Function and How to use the SQL Server CONCAT() function to join two or more strings into one.