SQL Plus Operator Overview

Usually, we use a SQL Plus (+) operator to perform SQL Server Concatenate operation with multiple fields together. We can specify space character as well in between these columns.

SQL CONCAT FUNCTION

Starting from SQL Server 2012, we have a new function to concatenate strings in SQL Server.


Difference between Concat() and (+) Operator

1) NULL values:

CONCAT and + don't operate the same. If any expression has the value NULL when using +, then NULL will be returned. For CONCAT, it will implicitly replace NULL with '', so NULL won't be returned.

if we have ANY ONE NULL value present in any fields, we get output of concatenate string as NULL with SQL Plus(+) operator.

If ALL the string passed in SQL CONCAT function have a NULL value, we get the output of this function also NULL.


SELECT 1+NULL
Output: NULL

SELECT  concat(1,null)
Output: 1

2) DataType Conversion :

CONCAT will also implicitly convert any values in the expression to an (n)varchar, where as + uses Data Type Precedence to determine the datatype and implicitly converts other values in the expression to the data type of the highest precedence.

SQL CONCAT function implicitly converts arguments to string types before concatenation. We can use SQL CONVERT function as well without converting the appropriate data type.


SELECT '1'+1
Output=2

SELECT concat(1,1)
Output: 11

If we concatenate string using the plus( +) operator, we need to use SQL CONVERT function to convert data types. Lets’ look this using the following example.


In the following query, we want to concatenate two strings. In this example, data type of first string is Text while another data type is a date.

Execute this query, and we get the following output.

Msg 402, Level 16, State 1, Line 3
The data types varchar and date are incompatible in the add operator.

We need to use SQL CONVERT function as per the following query, and it returns output without error message.

CONCAT examples

We do not need to use SQL CONVERT function to convert data type in SQL CONCAT function. It automatically does the conversion based on the input data type.

Concat examples

In the following table, we can see that data type conversion for input and output data types in SQL CONCAT function.

Input Data Type

Output Data Type

SQL CLR

NVARCHAR(MAX)

NVARCHAR(MAX)

NVARCHAR(MAX)

NVARCHAR(<=4000 characters)

NVARCHAR(<=4000 characters)

VARBINARY(MAX)

NVARCHAR(MAX)

All other data types

VARCHAR(<=8000) *if any parameters data type is NVARCHAR, the output value will be NVARCHAR(MAX)