CONCAT (Transact-SQL)

Syntax Show/Hide Syntax Details

Combines the string or the string representation of the value of each argument. Requires at least two arguments.

CONCAT ( string_expression1, string_expression2 [, string_expressionN ]... )

Description

CONCAT is a function that takes two or more arguments and returns a single string that is the result of concatenating those arguments.

It automatically converts non-string arguments to their string representation before concatenating them. This behavior is similar to the + operator when used with strings, but CONCAT avoids the implicit conversion errors that can occur with the + operator when dealing with numeric or date types.

The function can handle a variable number of arguments.

Arguments

Argument Description
string_expression1, string_expression2, ... string_expressionN One or more expressions that evaluate to string values or values that can be implicitly converted to string values. At least two arguments are required.

Return Value

Type: Varies based on input arguments. Typically returns VARCHAR or NVARCHAR.
Description: Returns the concatenated string of all input arguments. If any argument is NULL, CONCAT treats it as an empty string and does not return NULL unless all arguments are NULL.

Examples

Basic Concatenation

SELECT CONCAT('SQL', ' Server', ' is', ' great!');

Result:

SQL Server is great!

Concatenating with Different Data Types

CONCAT automatically converts non-string types.

SELECT CONCAT('The year is: ', 2023, ' and the temperature is ', 25.5, ' degrees.');

Result:

The year is: 2023 and the temperature is 25.5 degrees.

Handling NULL Values

CONCAT treats NULL values as empty strings.

SELECT CONCAT('First', NULL, 'Last');

Result:

FirstLast
SELECT CONCAT(NULL, NULL);

Result:

(empty string)

Concatenating from a Table

-- Assume a table named 'Customers' with columns 'FirstName' and 'LastName'
                    SELECT CONCAT(FirstName, ' ', LastName) AS FullName
                    FROM Customers
                    WHERE CustomerID = 101;

Remarks

  • The CONCAT function is a convenient way to combine strings, especially when dealing with mixed data types, as it handles implicit type conversions.
  • If you need to explicitly control type conversion or handle NULLs differently, you might consider using CAST or CONVERT in conjunction with the + operator.
  • The return data type of CONCAT is determined by the data types of the arguments. If all arguments are of a character data type (VARCHAR, NVARCHAR), the return type will be the character data type with the largest collation precedence. If other data types are present, they are converted to their string representation first.

See Also