Difference between Access SQL Concat and T-SQL Concat (1 Viewer)

[Rx_]

In Access there is the & and the +
In Access: "John " & null & "Smith" would equal John Smith
In Access: "John " + Null + "Smith" would be Null
the + is an operator - any operation with Null returns Null

When converting SQL statements using Concat operations in T-SQL, it is necessary to use the + operator. This was useful for me to remember the rules in the T-SQL side. See if you can guess the right answer.

CREATE TABLE #temp (
emp_name nvarchar(20) NULL, 
emp_middlename nvarchar(20) NULL,
emp_lastname nvarchar(20) NULL,
age int NOT NULL);

--Table #temp contains the following data
INSERT INTO #temp VALUES( 'Steve', NULL, 'Jones',43 );
INSERT INTO #temp VALUES( NULL, NULL, 'Moden',80 );
INSERT INTO #temp VALUES('Ron','Jay',NULL,82);

-- Then execute this
SELECT CONCAT(emp_name + ' ', emp_middlename + ' ',emp_lastname + ' ',age) AS 'Employee data'

 FROM #temp

What will this return?

Correct answer: Data returned includes Steve Jones 43,Moden 80,Ron Jay 82
Explanation: From http://msdn.microsoft.com/en-us/library/hh231515.aspx: CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string