CONCAT 函数中超过 2 列

2022-01-03 00:00:00 concat sql-server sql-server-2012

在 SQL Server 2012 中，我想将 5 列 concat 合并为 1，但在查询中它可以工作，但是当我放入视图时，它给了我一个错误，如

In SQL Server 2012 I want to concat 5 columns into 1 but in the query it works but when I put in in a view it gives me an error like

消息 174，级别 15，状态 1，第 3 行
CONCAT 函数需要 2 个参数.

Msg 174, Level 15, State 1, Line 3
The CONCAT function requires 2 argument(s).

有什么问题所以我可以解决它，因为 concat 是 concatenate 多于 1 列的好函数，因为如果它为 null，它们将使其为空..

What's the problem so I can fix it because concat is a good function for concatenate more than 1 column because if its null they make it empty..

代码:

SELECT        
   'Aan ' + A.Name AS 'Naam', 
   { fn CONCAT('T.a.v. ', C.Salutation + ' ', C.FirstName + ' ', C.MiddleName + ' ', C.LastName) } AS 'T.a.v.',   
   ISNULL(ISNULL(A.Address1_Line2, A.Address1_Line1), 
   C.Address1_Line2) AS 'Adres', 
   ISNULL(A.Address1_PostalCode + ' ' + A.Address1_City, A.Address2_PostalCode + ' ' + A.Address2_City) AS 'Woonplaats',
   'heer' + ' ' + ISNULL(C.MiddleName + ' ', N'') + ISNULL(C.LastName, N'') AS 'Aanhef'  
FROM            
    dbo.Account AS A 
FULL OUTER JOIN  
    dbo.Contact AS C ON A.Name = C.AccountIdName  
WHERE 
    (C.Salutation = 'Dhr.') AND (A.Name IS NOT NULL) AND (A.StatusCode = 1) 
    AND (ISNULL(C.StatusCode, 1) = 1) OR (C.Salutation = 'dhr.') AND (A.Name IS NOT NULL) AND (A.StatusCode = 1) AND (ISNULL(C.StatusCode, 1) = 1)

推荐答案

您的视图中的其他地方一定有错误！！

There must be an error somewhere else in your view!!

好的，那么我对你的代码所做的就是改变这一行

Ok, then what I did with your code was to change this line 

{ fn CONCAT('T.a.v. ', C.Salutation + ' ', C.FirstName + ' ', C.MiddleName + ' ', C.LastName) } AS 'T.a.v.'

到这里

CONCAT('T.a.v. ', C.Salutation + ' ', C.FirstName + ' ', C.MiddleName + ' ', C.LastName) AS 'T.a.v.'

只是为了解释代码的不同之处，带有 { fn ....} 的是一个规范函数，微软承诺它将适用于所有 ODBC 连接.

Just to explain the difference in code, is that the one with { fn ....} is a Canonical function and microsoft promise that it will work on all ODBC connections.

来自 MSDN:

规范函数是所有数据提供者都支持的函数，并且可以被所有查询技术使用.提供者无法扩展规范函数.

Canonical functions are functions that are supported by all data providers, and can be used by all querying technologies. Canonical functions cannot be extended by a provider.

相关文章