SQL Server CONCAT() Function

In SQL Server, the CONCAT() function is used to concatenate two or more strings into one string.

Syntax

CONCAT(string1, string2, ...)

Parameter explanation:

• string1, string2, …: The string values to concatenate.

Usage

The CONCAT() function is commonly used to concatenate two or more columns in a table, or to concatenate some fixed strings with columns in a table. It can also be used to concatenate multiple variables or constants to generate a string.

Examples

Example 1

Assume there is a table containing the following two columns of data:

The CONCAT() function can be used to concatenate the two columns together:

SELECT CONCAT(name, ' is ', age, ' years old.') AS info
FROM myTable;

The result is:

Example 2

Multiple strings can be concatenated together to generate a string, for example:

DECLARE @str1 VARCHAR(20) = 'Hello';
DECLARE @str2 VARCHAR(20) = ' world';
DECLARE @str3 VARCHAR(20) = '!';
SELECT CONCAT(@str1, @str2, @str3) AS result;

The result is:

NULL handling in SQL Server CONCAT()

Unlike the + operator, which yields NULL if any operand is NULL (when SET CONCAT_NULL_YIELDS_NULL ON is active), CONCAT() implicitly converts NULL arguments into empty strings '':

SELECT CONCAT('User: ', NULL, 'Active') AS result;

result
----------
User: Active

Conclusion

The CONCAT() function is used to concatenate two or more strings to generate a new string. It can be used to concatenate columns, variables, and constants in a table, and safely handles NULL values by converting them to empty strings.

To compare string concatenation operators, functions, and NULL handling across MySQL, PostgreSQL, SQLite, SQL Server, and Oracle, see the SQL String Concatenation by Database guide.