T-SQL CONCAT（）和+ [英] T-SQL CONCAT() and +

问题描述

我对这些MSDN页面的理解（{ CONCAT（Transact-SQL）}，{ +（String Concatenation）（Transact-SQL）}& { SET CONCAT_NULL_YIELDS_NULL（Transact-SQL）}）+将传播NULL，其中服务器'' s CONCAT_NULL_YIELDS_NULL 属性设置为ON，而 CONCAT（）会将它们隐式转换为空字符串。


我的代码，我的View的一个（相关的）部分是：

展开 | < span class =codeLinkonclick =selectAll（this）;>选择 | 换行 | 行号

解决方案

我试图重新创建你遇到的问题，但是我返回了预期的结果，CONCAT_NULL_YIELDS_NULL都设置为ON和OFF。您是否尝试删除+签名并在concat函数中包含空格？


~CK

不幸的是我无法测试这个，我们的SQL Server的版本太旧了。 concat是2012年的一项新功能。


但是，您似乎在2012年引入的新功能存在很多问题。也许数据库的设置具有较旧的兼容性等级？
https ：//msdn.microsoft.com/en-US/lib...=sql.105).aspx


但是，我可以确认CONCAT_NULL_YIELDS_NULL选项在2008 R2中按预期工作

感谢您的回复。


@Rabbit。

很棒的想法。我检查了我的，它提出了 SQL Server 2012（110）。


@CK。

我不确定我是否正确理解了你的建议。没有+我肯定会丢失NULL传播，这是代码的目的。


尽管如此，我尝试了这两个：

展开 | 选择 | Wrap | 行号

My understanding from these MSDN pages ({CONCAT (Transact-SQL)}, {+ (String Concatenation) (Transact-SQL)} & {SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)}) is that the + will propagate NULLs, where the server''s CONCAT_NULL_YIELDS_NULL property is set to ON as mine is, whereas CONCAT() will convert them implicitly to empty strings.

My code, a (relevant) fraction of my View, is :

Expand|Select|Wrap|Line Numbers

解决方案

I was trying to recreate the issue that you''re having but I am returning the expected results, with CONCAT_NULL_YIELDS_NULL both set to ON and OFF. Have you tried to remove the "+" sign and just include the space in the concat function?


~ CK

Unfortunately I can''t test this, our version of SQL Server is too old. And concat is an new function in 2012.

However, you seem to be having a lot of issues with the new functions introduced in 2012. Perhaps the database is set up with an older compatability level?
https://msdn.microsoft.com/en-US/lib...=sql.105).aspx

I can, however, confirm that the CONCAT_NULL_YIELDS_NULL option works as expected in 2008 R2

Thank you both for your responses.

@Rabbit.
Great thought. I checked mine though, and it came up with "SQL Server 2012 (110)".

@CK.
I''m not really sure if I correctly understand what you''re suggesting I try. Without the + I would surely lose the NULL propagation which is the purpose of the code.

Nevertheless, I tried both of these :

Expand|Select|Wrap|Line Numbers

这篇关于T-SQL CONCAT（）和+的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！